By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH games AS (
SELECT 1 AS id, 'one' AS title UNION ALL
SELECT 2, 'two' UNION ALL
SELECT 3, 'three'
),
actions AS (
SELECT 1 AS id, 1 AS game_id, 1 AS type, 2 AS user_id UNION ALL
SELECT 2, 2, 1, 2 UNION ALL
SELECT 3, 3, 1, 2 UNION ALL
SELECT 4, 1, 2, 1 UNION ALL
SELECT 5, 1, 2, 2 UNION ALL
SELECT 6, 1, 2, 3 UNION ALL
SELECT 7, 1, 3, 1 UNION ALL
SELECT 8, 1, 3, 1 UNION ALL
SELECT 9, 1, 3, 3 UNION ALL
SELECT 10, 2, 3, 1 UNION ALL
SELECT 11, 2, 3, 1
)
SELECT
g.id,
g.title,
a.n_actions_type_3,
a.n_actions_type_all
FROM games g
LEFT JOIN
(
SELECT game_id,
SUM(type = 3) AS n_actions_type_3,
COUNT(*) AS n_actions_type_all
FROM actions
GROUP BY game_id
) a
ON a.game_id = g.id;
| id | title | n_actions_type_3 | n_actions_type_all |
|---|---|---|---|
| 1 | one | 3 | 7 |
| 2 | two | 2 | 3 |
| 3 | three | 0 | 1 |