カウントした値を横に並べる

count_result_pivot.sql

MariaDB [test]> SELECT * FROM goods_status;
+------+--------+----------+
| id_g | status | category |
+------+--------+----------+
|    1 |      1 |      101 |
|    2 |      1 |      120 |
|    3 |      2 |      120 |
|    4 |      2 |      101 |
|    5 |      3 |      101 |
|    6 |      3 |      120 |
|    7 |      4 |      120 |
|    8 |      2 |      120 |
|    9 |      2 |      101 |
|   10 |      3 |      101 |
|   11 |      3 |      120 |
|   12 |      3 |      120 |
+------+--------+----------+
12 rows in set (0.000 sec)

MariaDB [test]> SELECT
    ->     SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END) AS '1',
    ->     SUM(CASE WHEN status = '2' THEN 1 ELSE 0 END) AS '2',
    ->     SUM(CASE WHEN status = '3' THEN 1 ELSE 0 END) AS '3',
    ->     SUM(CASE WHEN status = '4' THEN 1 ELSE 0 END) AS '4'
    -> FROM
    ->     goods_status
    -> ;
+------+------+------+------+
| 1    | 2    | 3    | 4    |
+------+------+------+------+
|    2 |    4 |    5 |    1 |
+------+------+------+------+
1 row in set (0.000 sec)

MariaDB [test]> SELECT
    ->     SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END) AS '1',
    ->     SUM(CASE WHEN status = '2' THEN 1 ELSE 0 END) AS '2',
    ->     SUM(CASE WHEN status = '3' THEN 1 ELSE 0 END) AS '3',
    ->     SUM(CASE WHEN status = '4' THEN 1 ELSE 0 END) AS '4',
    ->     (CASE
    ->         WHEN category = '101' THEN '101'
    ->         WHEN category = '120' THEN '120'
    ->         ELSE NULL
    ->     END) AS 'category'
    -> FROM
    ->     goods_status
    -> GROUP BY category
    -> ;
+------+------+------+------+----------+
| 1    | 2    | 3    | 4    | category |
+------+------+------+------+----------+
|    1 |    2 |    2 |    0 | 101      |
|    1 |    2 |    3 |    1 | 120      |
+------+------+------+------+----------+
2 rows in set, 1 warning (0.000 sec)