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

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

```sql
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)

```

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

```sql
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です。

```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が出てきます。

```sql
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関数を追加するとエラーが発生・・。
副問い合わせが必要。

```sql
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）](https://qiita.com/k24d/items/79bc4828c918dfeeac34)
[CASE式で条件分岐をSQL文に任せる]
(https://qiita.com/sfp_waterwalker/items/acc7f95f6ab5aa5412f3)


#追記
```bash
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)

```