Getting counts of 0 from a query with a double group by

Posted by Maltiriel on Stack Overflow See other posts from Stack Overflow or by Maltiriel
Published on 2012-03-19T16:07:54Z Indexed on 2012/03/19 18:03 UTC
Read the original article Hit count: 213

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql