COUNT issue across multiple tables
- by Kim
I am trying to count across 2 tables and I dont see whats wrong with my query yet I get a wrong result. User 2 does not exist in table_two, so the zero is correct.
SELECT t1.creator_user_id, COUNT(t1.creator_user_id), COUNT(t2.user_id)
FROM table_one AS t1
LEFT JOIN table_two AS t2 ON t2.user_id = t1.creator_user_id
GROUP BY t1.creator_user_id, t2.user_id
Actual result
1 192 192
2 9 0
Expected result
1 16 12
2 9 0
The result indicate a missing group by condition, but I already got both fields used.
Where am I wrong ?
Also, can I sum up all users that doesnt exist in table_two for t1 ?
Like user 3 exists 21 times in t1, then the results would be:
1 16 12 (users with > 0 in t2 will need their own row)
2 30 0 (user 2=9 + user 3=21 => 30)
Its okay for the user Id to be wrong for sum of t1 for all users with 0 in t2. If not possible, then I'll just do two queries.
