Getting counts of 0 from a query with a double group by
- by Maltiriel
I'm trying to write a query that gets the counts for a table (call it item) categorized by two different things, call them type and code. What I'm hoping for as output is the following:
Type Code Count
1 A 3
1 B 0
1 C 10
2 A 0
2 B 13
2 C 2
And so forth.
Both type and code are found in lookup tables, and each item can have just one type but more than one code, so there's also a pivot (aka junction or join) table for the codes. I have a query that can get this result:
Type Code Count
1 A 3
1 C 10
2 B 13
2 C 2
and it looks like (with join conditions omitted):
SELECT typelookup.name, codelookup.name, COUNT(item.id)
FROM typelookup
LEFT OUTER JOIN item JOIN itemcodepivot
RIGHT OUTER JOIN codelookup
GROUP BY typelookup.name, codelookup.name
Is there any way to alter this query to get the results I'm looking for? This is in MySQL, if that matters. I'm not actually sure this is possible all in one query, but if it is I'd really like to know how. Thanks for any ideas.
