Group By Clause biasanya digunakan ketika Anda menggunakan fungsi agregate seperti fungsi Count, Sum, Avg, dan lain sebagainya pada Select statement.
Sintak Dasar
Di bawah ini adalah sintak dasar perintah Group By Clause Di MySQL:
SELECT aggregate_function, column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2;
Contoh
Asumsikan Anda telah memiliki sebuah table "tbl_pegawai" dengan data-data sebagai berikut:
mysql> SELECT * FROM tbl_pegawai; +------------+------------------+---------------+----------+------------+------------+ | id_pegawai | nama_pegawai | jenis_kelamin | gaji | alamat | departemen | +------------+------------------+---------------+----------+------------+------------+ | 1 | Nursalim | L | 1000000 | Brebes | IT | | 2 | Naura Krasiva | P | 1500000 | Tegal | FIN | | 3 | Nurul Anawariyah | P | 2000000 | Pemalang | HRD | | 4 | Achmad Fathoni | L | 3000000 | Batang | IT | | 5 | Nurul Hikmah | P | 9000000 | Pekalongan | DIR | | 6 | Iskiyati | P | 3000000 | Semarang | ACC | | 7 | Dodi Al Farizi | L | 3000000 | Kendal | FIN | | 8 | Susanto | L | 4500000 | Cilacap | MKT | | 9 | Susanti | P | 43500000 | Banyumas | MKT | | 10 | Doni Nugroho | L | 3750000 | Pemalang | HRD | +------------+------------------+---------------+----------+------------+------------+ 10 rows in set (0.00 sec)Dari data diatas,
- Tampilkan jumlah pegawai berdasarkan jenis kelamin
mysql> SELECT count(nama_pegawai) AS jumlah_pegawai, jenis_kelamin -> FROM tbl_pegawai -> GROUP BY (jenis_kelamin); +----------------+---------------+ | jumlah_pegawai | jenis_kelamin | +----------------+---------------+ | 5 | L | | 5 | P | +----------------+---------------+ 2 rows in set (0.00 sec)
- Tampilkan jumlah pegawai berdasarkan departemen
mysql> SELECT count(nama_pegawai) AS jumlah_pegawai, departemen -> FROM tbl_pegawai -> GROUP BY departemen; +----------------+------------+ | jumlah_pegawai | departemen | +----------------+------------+ | 1 | ACC | | 1 | DIR | | 2 | FIN | | 2 | HRD | | 2 | IT | | 2 | MKT | +----------------+------------+ 6 rows in set (0.00 sec)
- Tampilkan total gaji pegawai per jenis kelamin
mysql> SELECT SUM(gaji) AS total_gaji, jenis_kelamin -> FROM tbl_pegawai -> GROUP BY jenis_kelamin; +------------+---------------+ | total_gaji | jenis_kelamin | +------------+---------------+ | 15250000 | L | | 59000000 | P | +------------+---------------+ 2 rows in set (0.00 sec)
Salam,
Nursalim
No comments:
Post a Comment