Menghasilkan Kesimpulan Subgrup – MySQL Dasar #56

Klausa GROUP by dapat digunakan untuk menghitung kesimpulan tiap subgrup dari sekelompok baris. Nantinya Group by dapat menata baris – baris kedalam grup – grup.

Lihat daftar isi : Menghasilkan Kesimpulan - MySQL Dasar #50

Untuk lebih jelasnya saya akan berikan beberapa contoh kasus Dimisalkan untuk mengetahui berapa jumlah data/baris yang terdapat pada tabel daftar_supir, kita bisa menggunakan fungsi agregat seperti count seperti berikut :

MariaDB [root93]> SELECT count(*) FROM daftar_supir;
+----------+
| count(*) |
+----------+
|      10  |
+----------+
1 row in set (0.001 sec)

Tetapi didalam contoh kasus lain dimisalkan Anda ingin menghitung nilai kesimpulan (count) dari setiap nama supir kemudian menampilkannya/mengelempokannya kedalam baris – baris bedasarkan nama supir, dimana data yang ditampilkan berupa daftar nama supir beserta berapa jumlah datanya didalam tabel

MariaDB [root93]> SELECT nama,
    -> COUNT(nama)
    -> FROM daftar_supir
    -> GROUP by nama;
+-------+-------------+
| nama  | COUNT(nama) |
+-------+-------------+
| Ben   |           2 |
| Henri |           2 |
| Henry |           4 |
| Susi  |           2 |
+-------+-------------+
4 rows in set (0.001 sec)

Perhatikan bahwa fungsi GROUP by juga mengeliminasi duplikat, dimana data tiap subgrup akan dikelompokan menjadi group – group. Selain menggunakan fungsi COUNT, Anda juga bisa menggunakan fungsi agregat/kesimpulan lainnya seperti SUM atau AVG. Dimislakan Anda ingin mengetahui berapa jumlah total km da rata – rata km dari tabel daftar_supir, maka Anda bisa menuliskan query berikut :

MariaDB [root93]> SELECT nama,
    -> SUM(km) AS 'total km',
    -> AVG(km) AS 'total rerata km'
    -> FROM daftar_supir
    -> GROUP by nama;
+-------+----------+-----------------+
| nama  | total km | total rerata km |
+-------+----------+-----------------+
| Ben   |      968 |        484.0000 |
| Henri |      747 |        373.5000 |
| Henry |     1425 |        356.2500 |
| Susi  |      893 |        446.5000 |
+-------+----------+-----------------+
4 rows in set (0.001 sec)

Fungsi agregat seperti count didalam klausa group by juga bisa ditulis dengan cara lain. Misalnya sebelumnya count(ekspresi), Anda bisa saja menuliskannya menjadi count(*). Dimisalkan disini kit akan menghitung berapa banyak pesan yang dikirimkan oleh tiap pengirim pada tabel mail :

MariaDB [root93]> SELECT srcuser, COUNT(*) FROM mail
    -> GROUP by srcuser;
+---------+----------+
| srcuser | COUNT(*) |
+---------+----------+
| barb    |        3 |
| gene    |        5 |
| phil    |        3 |
| tricia  |        2 |
+---------+----------+
4 rows in set (0.001 sec)

Untuk mengetahui lebih sepesifik dan mengetahui berapa banyak pesan yang dikirim pengirim ke tiap host, kita juga bisa menuliskan query seperti berikut :

MariaDB [root93]> SELECT srcuser, srchost, COUNT(*) FROM mail
    -> GROUP by srcuser, srchost;
+---------+---------+----------+
| srcuser | srchost | COUNT(*) |
+---------+---------+----------+
| barb    | saturn  |        2 |
| barb    | venus   |        1 |
| gene    | mars    |        1 |
| gene    | saturn  |        2 |
| gene    | venus   |        2 |
| phil    | mars    |        2 |
| phil    | venus   |        1 |
| tricia  | mars    |        1 |
| tricia  | saturn  |        1 |
+---------+---------+----------+
9 rows in set (0.001 sec)

Selain menggunakan COUNT, SUM dan AVG, kita juga bisa menggunakan fungsi lainnya seperti MIN dan MAX. Dengan Klausa Group by, kedua fungsi tersebut dapat memberitahu kita nilai terendah dan tertinggi untuk setiap grup. Contoh query dibawah ini akan menampilkan nilai tertinggi dari tabel mail lalu mengelompokan berdasarkan pengirim pesan.

MariaDB [root93]> SELECT srcuser, MAX(ukuran), MAX(t)
    -> FROM mail
    -> GROUP by srcuser;
+---------+-------------+---------------------+
| srcuser | MAX(ukuran) | MAX(t)              |
+---------+-------------+---------------------+
| barb    |       98161 | 2021-10-13 12:32:41 |
| gene    |       32682 | 2021-10-13 10:43:39 |
| phil    |        7823 | 2021-10-13 10:42:32 |
| tricia  |      239238 | 2021-10-13 10:40:45 |
+---------+-------------+---------------------+
4 rows in set (0.001 sec)

Contoh lainnya kita juga dapat mengelompokan nilai berdasarkan ukuran dari setiap pasangan pengirim dan penerima

MariaDB [root93]> SELECT srcuser, dstuser, MAX(ukuran) FROM mail
    -> GROUP by ukuran;
+---------+---------+-------------+
| srcuser | dstuser | MAX(ukuran) |
+---------+---------+-------------+
| barb    | tricia  |         271 |
| phil    | phil    |        1048 |
| gene    | barb    |        2291 |
| gene    | gene    |        3623 |
| gene    | barb    |        3872 |
| phil    | tricia  |        5781 |
| phil    | barb    |        7823 |
| tricia  | gene    |       19263 |
| gene    | gene    |       22332 |
| gene    | gene    |       32682 |
| barb    | tricia  |       58274 |
| barb    | barb    |       98161 |
| tricia  | phil    |      239238 |
+---------+---------+-------------+
13 rows in set (0.001 sec)

Selanjutnya dimisalkan Anda ingin mengetahui daftar supir berdasarkan perjalanan terjauh tiap supir dari tabel daftar_supir, maka query yang dihasilkannya kurang lebih akan ditampilkan seperti berikut

MariaDB [root93]> SELECT nama, MAX(km) AS 'terjauh'
    -> FROM daftar_supir
    -> GROUP by nama;
+-------+---------+
| nama  | terjauh |
+-------+---------+
| Ben   |     837 |
| Henri |     380 |
| Henry |     574 |
| Susi  |     502 |
+-------+---------+
4 rows in set (0.001 sec)

Tetapi jika misalnya Anda juga ingin menampilkan tanggal kapan perjalanan terpanjang tersebut dilakukan oleh tiap supir, maka mungkin akan terjadi kesalahan seperti berikut

MariaDB [root93]> SELECT nama, tanggal_travel, MAX(km) AS 'terjauh' FROM daftar_supir GROUP by nama;
+-------+----------------+---------+
| nama  | tanggal_travel | terjauh |
+-------+----------------+---------+
| Ben   | 2014-11-29     |     837 |
| Henri | 2014-11-27     |     380 |
| Henry | 2014-11-29     |     574 |
| Susi  | 2014-11-29     |     502 |
+-------+----------------+---------+
4 rows in set (0.001 sec)

sekilas memang tidak ada yang salah dari hasil query yang ditampilkan diatas, tetapi jika Anda melihat data pada tabel daftar_supir secara keseluruhan maka Anda akan menemukan kesalahan dimana tanggal_travel yang ditampilkan tidak sesuai dengan tanggal travel yang memiliki perjalanan terjauh

MariaDB [root93]> SELECT * FROM daftar_supir;
+--------+-------+----------------+------+
| id_rek | nama  | tanggal_travel | km   |
+--------+-------+----------------+------+
|      1 | Susi  | 2014-11-29     |  391 |
|      2 | Henry | 2014-11-29     |  300 |
|      3 | Henri | 2014-11-27     |  367 |
|      4 | Ben   | 2014-11-29     |  131 |
|      5 | Henry | 2014-11-26     |  278 |
|      6 | Susi  | 2014-12-02     |  502 |
|      7 | Henry | 2014-12-01     |  273 |
|      8 | Ben   | 2014-12-02     |  837 |
|      9 | Henry | 2014-11-30     |  574 |
|     10 | Henri | 2014-11-26     |  380 |
+--------+-------+----------------+------+
10 rows in set (0.001 sec)

Hal ini disebabkan ketika Anda menuliskan klausa GROUP by pada sebuah query, maka nilai – nilai yang Anda seleksi adalah kolom – kolom yang dikelompokan berdasarkan nilai – nilai yang dihitung kesimpulannya menggunakan fungsi agregat. Oleh sebab itu untuk mengatasi masalah tersebu, Anda bisa menggunakan parameter MAX CONCAT seperti berikut :

MariaDB [root93]> SELECT nama,
    -> SUBSTRING(MAX(CONCAT(LPAD(km,3,''),tanggal_travel)),4) AS tanggal,
    -> LEFT(MAX(CONCAT(LPAD(km,3,''),tanggal_travel)),3) AS terjauh
    -> FROM daftar_supir
    -> GROUP by nama;
+-------+------------+---------+
| nama  | tanggal    | terjauh |
+-------+------------+---------+
| Ben   | 2014-12-02 | 837     |
| Henri | 2014-11-26 | 380     |
| Henry | 2014-11-30 | 574     |
| Susi  | 2014-12-02 | 502     |
+-------+------------+---------+
4 rows in set (0.001 sec)

Baca Selanjutnya : Kesimpulan pada nilai NULL – MySQL Dasar #57

Ahmad Zaelani

Seorang laki - laki biasa yang senang duduk sendirian di pinggir pantai, pengagum embun pagi dan lembayung senja, suka blogging, coding dan jogging. Moto hidupnya adalah hidup santai dan simple

Menghasilkan Kesimpulan Subgrup – MySQL Dasar #56

0 Response to "Menghasilkan Kesimpulan Subgrup – MySQL Dasar #56 "

Post a Comment