two where conditions in a mysql query

Question

I have a table like below

|date|dom|guid|pid|errors|QA|comm|
|2010-03-22|xxxx.com|jsd3j234j|ab|Yes|xxxxxx|bad|
|2010-03-22|xxxx.com|jsd3j234j|ab|No|xxxxxx||
|2010-03-22|xxxx.com|jsd3j234j|if|Yes|xxxxxx|bad|
|2010-03-22|xxxx.com|jsd3j234j|if|No|xxxxxx||
|2010-03-22|xxxx.com|jsd3j234j|he|Yes|xxxxxx|bad|
|2010-03-22|xxxx.com|jsd3j234j|he|No|xxxxxx||

I want to retrieve the total count of "dom" referred to each "QA" and also I need the count of "errors" detected by the "QA"

SELECT date, count(dom), QA FROM reports WHERE date="2010-03-22" GROUP BY QA

|2010-03-22|2|ab|
|2010-03-22|2|if|
|2010-03-22|2|he|

SELECT date, count(dom), count(errors), QA FROM reports WHERE errors="Yes" GROUP BY QA

|2010-03-22|1|ab|
|2010-03-22|1|if|
|2010-03-22|1|he|

I want to combine the above two queries, is it possible.

If I use the below query, I am not getting the desired result.

SELECT date, count(dom), QA, count(errors) FROM reports WHERE date="2010-03-22" AND errors="Yes" GROUP BY QA

I want the below output

|2010-03-22|2|ab|1|
|2010-03-22|2|if|1|
|2010-03-22|2|he|1|

Please help me.

Answer 1

Try using OR in place of AND:

.... date="2010-03-22" OR errors="Yes" GROUP BY QA

Answer 2

You can do this with:

SELECT date, COUNT(dom), QA, COUNT(NULLIF(errors, 'No')) FROM reports WHERE date="2010-03-22" GROUP BY QA

To explain how this works: COUNT returns the number of non-null values. We can use this to our advantage by turning errors with 'No' into NULL, so COUNTwon't count them. We do this with the NULLIF function, which returns NULL if the first and second arguments are equal.