{Kurung Kurawal}

(My)SQL Query untuk mengambil row sebagai kolom

konglie

Jadi ceritanya ada sahabat membutuhkan penampilan laporan untuk aplikasi yang sedang dia buat dengan menampilkan jumlah item sesuai dengan jenis item akan tetapi dihitung per user per item tersebut. Item disini hanya ada 2 saja, jadinya memudahkan penyusunan kata-kata indah SQL nya. Langsung saja ke TKP.

Struktur table (saya buat mirip dengan table asli di aplikasi tersebut).

1
2
3
4
5
6
7

mysql>desc my_table;
+-------+---------+------+-----+---------+-------+
| FIELD | TYPE    | NULL | KEY | DEFAULT | Extra |
+-------+---------+------+-----+---------+-------+
| id    | INT(11) | YES  |     | NULL    |       |
| item  | INT(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

data yang ada adalah sebagai berikut

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

mysql> SELECT * FROM my_table;
+------+------+
| id   | item |
+------+------+
|    1 |  100 |
|    1 |  100 |
|    1 |  100 |
|    1 |  100 |
|    1 |  100 |
|    1 |  500 |
|    1 |  500 |
|    1 |  500 |
|    1 |  500 |
|    2 |  100 |
|    2 |  100 |
|    2 |  100 |
|    2 |  100 |
|    2 |  100 |
|    2 |  100 |
|    2 |  500 |
|    2 |  500 |
|    2 |  500 |
|    2 |  500 |
|    2 |  500 |
|    2 |  500 |
|    2 |  100 |
+------+------+
22 ROWS IN SET (0.00 sec)

Data yang diinginkan adalah

1
2
3
4
5
6

+------+-----------------+-----------------+
| id   | jumlah_item_100 | jumlah_item_500 |
+------+-----------------+-----------------+
|    1 |               5 |               4 |
|    2 |               7 |               6 |
+------+-----------------+-----------------+

Mari kita kerjakan.

Pertama kita butuh menghitung jumlah item untuk masing-masing item dan masing-masing user (id).

1
2
3
4
5
6

SELECT
        id,
        item,
        COUNT(item) AS n
FROM my_table
GROUP BY 1,2

Hasilnya

1
2
3
4
5
6
7
8

+------+------+---+
| id   | item | n |
+------+------+---+
|    1 |  100 | 5 |
|    1 |  500 | 4 |
|    2 |  100 | 7 |
|    2 |  500 | 6 |
+------+------+---+

Disini kita sudah bisa melihat jumlah user dan jumlah item yang ada (diindikasikan oleh n). Kita perlu menyederhanakannya lebih lanjut. Agar field id ditampilkan secara unique (atau DISTINCT), hanya saja kita tidak dapat menggunakan query distinct karena kita butuh semua id yang tersedia. Caranya adalah dengan memisahkan kedua item menjadi field atau column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT
        id,
        CASE item
                WHEN 100 THEN n
                ELSE 0
        END AS item100,
        CASE item
                WHEN 500 THEN n
                ELSE 0
        END AS item500
FROM (
        SELECT
                id,
                item,
                COUNT(item) AS n
        FROM
                my_table
        GROUP BY 1,2
        ) AS p

Hasilnya,

1
2
3
4
5
6
7
8

+------+---------+---------+
| id   | item100 | item500 |
+------+---------+---------+
|    1 |       5 |       0 |
|    1 |       0 |       4 |
|    2 |       7 |       0 |
|    2 |       0 |       6 |
+------+---------+---------+

Sudah semakin terlihat jelas bukan? Langkah terakhir adalah menghilangkan baris yang tidak perlu, yaitu baris dimana item yang berisi 0 tidak perlu ditampilkan. Algoritma yang digunakan salah satunya adalah sebagai berikut

Ambil semua baris, jumlah semua item per id. Pada hasil diatas, misalnya kita ambil id 1, maka jumlah untuk item100 adalah 5 + 0 = 5, dan jumlah untuk item500 adalah 0 + 4 = 4, dengan demikian penjumlah total tidak akan mengubah nilai, dikarenakan pada satu baris hanya satu field item saja yang memiliki nilai, dan lainnya adalah 0. Untuk penjumlahan, SQL query (MySQL atau semua database server pada umumnya) memiliki fungsi SUM()

Jadi, querynya adalah

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

SELECT
        id,
        SUM(item100) AS jumlah_item_100,
        SUM(item500) AS jumlah_item_500
FROM
(
        SELECT
                id,
                CASE item
                        WHEN 100 THEN n
                        ELSE 0
                END AS item100,
                CASE item
                        WHEN 500 THEN n
                        ELSE 0
                END AS item500
        FROM (
                SELECT
                        id,
                        item,
                        COUNT(item) AS n
                FROM
                        my_table
                GROUP BY 1,2
                ) AS p
) AS y
GROUP BY id;

Hasil akhirnya adalah

1
2
3
4
5
6

+------+-----------------+-----------------+
| id   | jumlah_item_100 | jumlah_item_500 |
+------+-----------------+-----------------+
|    1 |               5 |               4 |
|    2 |               7 |               6 |
+------+-----------------+-----------------+

Semoga postingan blog ini dapat memberi manfaat untuk kita semua.