This post is also available in:
English
mysql>desc my_table;
+-------+---------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | Extra |
+-------+---------+------+-----+---------+-------+
| id | INT(11) | YES | | NULL | |
| item | INT(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+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)+------+-----------------+-----------------+
| id | jumlah_item_100 | jumlah_item_500 |
+------+-----------------+-----------------+
| 1 | 5 | 4 |
| 2 | 7 | 6 |
+------+-----------------+-----------------+SELECT
id,
item,
COUNT(item) AS n
FROM my_table
GROUP BY 1,2+------+------+---+
| id | item | n |
+------+------+---+
| 1 | 100 | 5 |
| 1 | 500 | 4 |
| 2 | 100 | 7 |
| 2 | 500 | 6 |
+------+------+---+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+------+---------+---------+
| id | item100 | item500 |
+------+---------+---------+
| 1 | 5 | 0 |
| 1 | 0 | 4 |
| 2 | 7 | 0 |
| 2 | 0 | 6 |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;+------+-----------------+-----------------+
| id | jumlah_item_100 | jumlah_item_500 |
+------+-----------------+-----------------+
| 1 | 5 | 4 |
| 2 | 7 | 6 |
