MySQL query count with subsort by type

Question

I was wondering if anyone can help with this

I have the following tables

user_ids {
    uUID
    name
}

types {
    tUID
    name
}

activity {
    aUID
    user_id
    type_id
    date
}

so users would contain

uUID    name
1       Bob
2       Mark
3       Peter

types would be

tUID    name
1       Baseball
2       Football
3       Cricket

activity would be

aUID    user_id    type_id    date
1         1           2
2         1           2
3         1           3
4         2           1
5         2           3
6         2           1
7         3           3
8         3           3
9         3           3

now what I would like to get is the following table back

Name   Type      Count of type
Bob    Baseball  0
Bob    Football  2
Bob    Cricket   1
Mark   Baseball  2
Mark   Football  0
Mark   Cricket   1
Peter  Baseball  0
Peter  Football  0
Peter  Cricket   3

I can do this with single query's per user id but would like to know if there is a way to do this with a single query instead.

Thanks

Martin

Answer 1

Try like this

With zero

SELECT X.Name ,X.Type,
SUM(CASE WHEN A.user_id IS NULL THEN 0 ELSE 1 END ) as `Count of type`
FROM
(
    SELECT T.Name AS Type,U.name AS NAME,U.uUID,T.tuid FROM types T,user_ids U
)X  LEFT JOIN activity A ON A.type_id = X.tuid AND A.user_id =X.uUID
GROUP BY X.Name ,X.Type
ORDER BY X.Name ,X.Type

Without zero

SELECT U.name AS Name, 
       T.name AS Type, 
       SUM(CASE WHEN A.user_id IS NULL THEN 0 ELSE 1 END ) as `Count of type`
FROM types T
LEFT JOIN activity a on T.tuid=A.type_id
JOIN user_ids U on A.user_id = U.uUID
GROUP BY U.name, T.name

Answer 2

You just have to left join type and activity tables and group by user and type:

select u.name as Name, t.name as Type, COUNT(a.user_id) as `Count of type`
from type t
left join activity a on t.tuid=a.type_id
left join users u on a.user_id = u.uUID
group by u.name, t.name