Pernyataan GROUP BY dalam MySQL

MySQL: Pernyataan GROUP BY  untuk Pengelompokan Data Agregasi

Oleh: Fairuz el Said

Pernyataan GROUP BYPernyataan GROUP BY dalam MySQL adalah pernyataan yang dirangkai dengan pernyataan SELECT dalam hubungannya dengan fungsi agregat untuk mengelompokkan hasil query untuk satu atau beberapa field (kolom). Fungsi agregat  dalam MySQL adalah fungsi yang menerima koleksi nilai dan mengembalikan nilai tunggal sebagai hasilnya, seperti: jumlah data, nilai minimun, nilai maximum dan nilai rata-rata.

Fungsi Aggregat yang biasanya digunakan dalam pernyataan GROUP BY, yaitu:

SUM digunakan untuk menghitung total nilai dari kolom tertentu
COUNT digunakan untuk menghitung jumlah record.
AVG digunakan untuk menampikan nilai rata-rata dari suatu kolom
MAX digunakan untuk menampikan nilai tertinggi dari suatu kolom
MIN digunakan untuk menampikan nilai terendah dari suatu kolom

Sintaks Penyataan GROUP BY

SELECT nama_kolom, fungs_agregat(nama_kolom)
FROM nama_tabel
WHERE syarat
GROUP BY nama_kolom;

Contoh Pernyataan GROUP BY

Penyiapan Data untuk latihan Pernyataan GROUP BY

Sebelum latihan pernyataan GROUP BY siapkan terlebih dahulu tabel yang akan digunakan

  • Buat tabel buku
mysql> CREATE TABLE buku (
    ->   kode_buku CHAR(5) PRIMARY KEY,
    ->   judul_buku VARCHAR(50),
    ->   pengarang VARCHAR(30),
    ->   penerbit VARCHAR(30),
    ->   tahun YEAR,
    ->   kategori VARCHAR(30),
    ->   harga INT(7),
    ->   tgl_inventaris DATE
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> DESC buku;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| kode_buku      | char(5)     | NO   | PRI | NULL    |       |
| judul_buku     | varchar(50) | YES  |     | NULL    |       |
| pengarang      | varchar(30) | YES  |     | NULL    |       |
| penerbit       | varchar(30) | YES  |     | NULL    |       |
| tahun          | year(4)     | YES  |     | NULL    |       |
| kategori       | varchar(30) | YES  |     | NULL    |       |
| harga          | int(7)      | YES  |     | NULL    |       |
| tgl_inventaris | date        | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
  • Isi data sebagai berikut:
mysql> INSERT INTO buku VALUES
 -> ('B0001', 'Harry Potter', 'JK Rowling', 'British Press', 2013, 'Fiksi', 50000, '201-03-01'),
 -> ('B0002', 'Sistem Basis Data', 'Abdul Kadir', 'Andi Offset', 2013, 'Buku Teks', 40000, '2015-03-01'),
 -> ('B0003', 'Sistem Basis Data', 'Fathansyah', 'ITB Press', 2013, 'Buku Teks', 30000, '2015-03-01'),
 -> ('B0004', 'Prophet Muhammad', 'Amir Abdullah', 'Madina Press', 2014, 'Biografi', 45000, '2015-03-01'),
 -> ('B0005', 'Ketika Cinta Bertasbih', 'Habiburahaman ES', 'Madina Press', 2014, 'Fiksi', 75000, '2015-02-01'),
 -> ('B0006', 'Pemrograman Basis Data', 'Abdul Kadir', 'Andi Offset', 2015, 'Buku Teks', 67000, '2015-02-01');
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM buku;
+-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+
| kode_buku | judul_buku             | pengarang        | penerbit      | tahun | kategori  | harga| tgl_inventaris |
+-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+
| B0001     | Harry Potter           | JK Rowling       | British Press |  2013 | Fiksi     | 50000| 2015-03-01     |
| B0002     | Sistem Basis Data      | Abdul Kadir      | Andi Offset   |  2013 | Buku Teks | 40000| 2015-03-01     |
| B0003     | Sistem Basis Data      | Fathansyah       | ITB Press     |  2013 | Buku Teks | 30000| 2015-03-01     |
| B0004     | Prophet Muhammad       | Amir Abdullah    | Madina Press  |  2014 | Biografi  | 45000| 2015-03-01     |
| B0005     | Ketika Cinta Bertasbih | Habiburahaman ES | Madina Press  |  2014 | Fiksi     | 75000| 2015-02-01     |
| B0006     | Pemrograman Basis Data | Abdul Kadir      | Andi Offset   |  2015 | Buku Teks | 67000| 2015-02-01     |
+-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+
6 rows in set (0.00 sec)

Latihan Pernyataan GROUP BY

  • Tampilkan banyaknya buku dari masing-masing pengarang dengan menggunakan pernyataan GROUP BY
mysql> SELECT pengarang, COUNT(pengarang)
    -> FROM buku
    -> GROUP BY pengarang;
+------------------+------------------+
| pengarang        | count(pengarang) |
+------------------+------------------+
| Abdul Kadir      |                2 |
| Amir Abdullah    |                1 |
| Fathansyah       |                1 |
| Habiburahaman ES |                1 |
| JK Rowling       |                1 |
+------------------+------------------+
5 rows in set (0.05 sec)

  • Tampilkan banyaknya buku dari masing-masing pengarang gunakan kolom alias jumlah_buku dengan menggunakan pernyataan GROUP BY
mysql> SELECT pengarang, COUNT(pengarang) AS jumlah_buku
    -> FROM buku
    -> GROUP BY pengarang;
+------------------+-------------+
| pengarang        | jumlah_buku |
+------------------+-------------+
| Abdul Kadir      |           2 |
| Amir Abdullah    |           1 |
| Fathansyah       |           1 |
| Habiburahaman ES |           1 |
| JK Rowling       |           1 |
+------------------+-------------+
5 rows in set (0.03 sec)

  • Dengan pernyataan GROUP BY, tampilkan jumlah harga buku yang dikelompokkan berdasarkan tgl_inventaris. Buat dengan alias total_harga dengan menggunakan pernyataan GROUP BY
mysql> SELECT tgl_inventaris, SUM(harga) AS total_harga
    -> FROM buku
    -> GROUP BY tgl_inventaris;
+----------------+-------------+
| tgl_inventaris | total_harga |
+----------------+-------------+
| 2015-02-01     |      142000 |
| 2015-03-01     |      165000 |
+----------------+-------------+
2 rows in set (0.00 sec)

  • Tampilkan banyaknya buku (qty), jumlah harga buku (total_harga) yang dikelompokkan berdasarkan tgl_inventaris. Buat dengan alias total_harga dengan menggunakan pernyataan GROUP BY
mysql> SELECT tgl_inventaris, COUNT(judul_buku) AS qty, SUM(harga) AS total_harga
    -> FROM buku
    -> GROUP BY tgl_inventaris;
+----------------+-----+-------------+
| tgl_inventaris | qty | total_harga |
+----------------+-----+-------------+
| 2015-02-01     |   2 |      142000 |
| 2015-03-01     |   4 |      165000 |
+----------------+-----+-------------+
2 rows in set (0.00 sec)

  • Tampilkan harga buku termahal, rata-rata dan termurah yang dikelompokkan berdasarkan kategori.
mysql> SELECT kategori, MAX(harga) AS termahal,
    ->        AVG(harga) AS rata_rata, MIN(harga) AS Termurah
    -> FROM buku
    -> GROUP BY kategori;
+-----------+----------+------------+----------+
| kategori  | termahal | rata_rata  | Termurah |
+-----------+----------+------------+----------+
| Biografi  |    45000 | 45000.0000 |    45000 |
| Buku Teks |    67000 | 45666.6667 |    30000 |
| Fiksi     |    75000 | 62500.0000 |    50000 |
+-----------+----------+------------+----------+
3 rows in set (0.00 sec)
  • Tampilkan jumlah record masing-masing kategori selain kategori=’Fiksi’.
mysql> SELECT kategori, COUNT(*)
    -> FROM buku
    -> WHERE NOT(kategori='Fiksi')
    -> GROUP BY kategori;
+-----------+----------+
| kategori  | COUNT(*) |
+-----------+----------+
| Biografi  |        1 |
| Buku Teks |        3 |
+-----------+----------+
2 rows in set (0.00 sec)

Demikian pokok bahasan Pernyataan GROUP BY dalam MySQL. Semoga bermanfaat. Selamat belajar dan berbagi.

Sumber

 

Tuliskan komentar anda

Komentar

Tinggalkan Balasan