MySQL 1回のクエリで取れたデータを別々の条件別にカウントしたい

SELECT 全体のレコード数, 条件A の総数, 条件B の総数
FROM テーブル
WHERE 条件
GROUP BY カラム

こんな感じで特定の条件でグループ化したデータをグルーピングされた中で、さらに条件別に数を出したい。
例えばユーザーの購入レコードがあって、ユーザー毎の購入総数・カテゴリAの購入数・カテゴリBの購入数を表示したいみたいなケース。

データ

こんな感じの購入履歴のデータ

mysql> select * from purchase_log;
+----+---------+---------+----------+
| id | user_id | item_id | category |
+----+---------+---------+----------+
|  1 |       1 |       1 |        1 |
|  2 |       1 |       2 |        2 |
|  3 |       1 |       3 |        1 |
|  4 |       2 |       1 |        1 |
|  5 |       2 |       2 |        2 |
|  6 |       2 |       4 |        2 |
|  7 |       3 |       2 |        2 |
|  8 |       3 |       3 |        1 |
+----+---------+---------+----------+

user_id ごとの総数とカテゴリー別の購入数を取りたい

総数は `user_id` 毎にグループ化してレコード数を count すればOK

mysql> SELECT user_id, count(*) FROM purchase_log GROUP BY user_id;
+---------+----------+
| user_id | count(*) |
+---------+----------+
|       1 |        3 |
|       2 |        3 |
|       3 |        2 |
+---------+----------+

count の中で if 文を使って条件付きのレコード数を出力できる

IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.
cf. MySQL :: MySQL 8.0 Reference Manual :: 12.5 Flow Control Functions

count(if(条件, 1, null))

の形で条件にマッチしたレコード数だけが取れる

mysql> SELECT user_id, count(*) as total,
  count(if(category = 1, 1, null)) as 'cat 1',
  count(if(category = 2, 1, null)) as 'cat 2'
FROM purchase_log
GROUP BY user_id;
+---------+-------+-------+-------+
| user_id | total | cat 1 | cat 2 |
+---------+-------+-------+-------+
|       1 |     3 |     2 |     1 |
|       2 |     3 |     1 |     2 |
|       3 |     2 |     1 |     1 |
+---------+-------+-------+-------+

⚠️ if の else のときの条件を `0` にすると正しくカウントされない

count(if(条件, 1, 0))

count なので条件に合わない時は 0 でも良いのかなと思ったが、上記のような方法では 0 も存在するという判断でカウントされるので上手く行かない

mysql> SELECT user_id, count(*) as total,
  count(if(category = 1, 1, 0)) as 'cat 1',
  count(if(category = 2, 1, 0)) as 'cat 2'
FROM purchase_log
GROUP BY user_id;
+---------+-------+-------+-------+
| user_id | total | cat 1 | cat 2 |
+---------+-------+-------+-------+
|       1 |     3 |     3 |     3 |
|       2 |     3 |     3 |     3 |
|       3 |     2 |     2 |     2 |
+---------+-------+-------+-------+

1, 0 にする場合は SUM を使えば正しい値になる。

mysql> SELECT user_id, count(*) as total,
  sum(if(category = 1, 1, 0)) as 'cat 1',
  sum(if(category = 2, 1, 0)) as 'cat 2'
FROM purchase_log
GROUP BY user_id;
+---------+-------+-------+-------+
| user_id | total | cat 1 | cat 2 |
+---------+-------+-------+-------+
|       1 |     3 |     2 |     1 |
|       2 |     3 |     1 |     2 |
|       3 |     2 |     1 |     1 |
+---------+-------+-------+-------+