SQLでグループごとにカウントした結果を横に並べる

以下のようなテーブルから
各ステータスごとにカウントした結果を1行で出力したいなぁと思い調べて(検索して)みました。

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

ちなみに、普通にカウントすると縦に並んで出力されます。

MariaDB [test]> SELECT status, COUNT(id_g) FROM goods_status GROUP BY status;
+--------+-------------+
| status | count(id_g) |
+--------+-------------+
|      1 |           2 |
|      2 |           4 |
|      3 |           5 |
|      4 |           1 |
+--------+-------------+
4 rows in set (0.001 sec)

やったこと

調べてみるとCASEを使って縦横変換できるようなのでやってみました。

以下が作成したSQLです。

MariaDB [test]> SELECT
    -> MAX(CASE WHEN status = '1' THEN count END) AS '1',
    -> MAX(CASE WHEN status = '2' THEN count END) AS '2',
    -> MAX(CASE WHEN status = '3' THEN count END) AS '3',
    -> MAX(CASE WHEN status = '4' THEN count END) AS '4'
    -> FROM
    -> (SELECT status, count(id_g) AS count FROM goods_status GROUP BY status) AS gs
    -> ;
+------+------+------+------+
| 1    | 2    | 3    | 4    |
+------+------+------+------+
|    2 |    4 |    5 |    1 |
+------+------+------+------+
1 row in set (0.001 sec)

※コメントでこれよりわかりやすいSQLを教えて頂きました。
　是非参照することをお勧めします。

おまけ

以下、上記の完成形までに試行錯誤したSQL達。

MAX関数がないとNULLが出てきます。

MariaDB [test]> SELECT
    -> CASE WHEN status = '1' THEN COUNT(status) END AS '1',
    -> CASE WHEN status = '2' THEN COUNT(status) END AS '2',
    -> CASE WHEN status = '3' THEN COUNT(status) END AS '3',
    -> CASE WHEN status = '4' THEN COUNT(status) END AS '4'
    -> FROM
    -> goods_status
    -> GROUP BY
    -> status
    -> ;
+------+------+------+------+
| 1    | 2    | 3    | 4    |
+------+------+------+------+
|    2 | NULL | NULL | NULL |
| NULL |    4 | NULL | NULL |
| NULL | NULL |    5 | NULL |
| NULL | NULL | NULL |    1 |
+------+------+------+------+
4 rows in set (0.000 sec)

MAX関数を追加するとエラーが発生・・。
副問い合わせが必要。

MariaDB [test]> SELECT
    -> MAX(CASE WHEN status = '1' THEN COUNT(status) END) AS '1',
    -> MAX(CASE WHEN status = '2' THEN COUNT(status) END) AS '2',
    -> MAX(CASE WHEN status = '3' THEN COUNT(status) END) AS '3',
    -> MAX(CASE WHEN status = '4' THEN COUNT(status) END) AS '4'
    -> FROM
    -> goods_status
    -> GROUP BY
    -> status
    -> ;
ERROR 1111 (HY000): Invalid use of group function

以上です。

参考

SQL で縦横変換まとめ（pivot と unpivot）
CASE式で条件分岐を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',
    ->     (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)