CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 1690

query with multiple count statements

I would like to combine the following two queries, so that I end up with three columns: RelativePath, field = null/empty string, field with value. I can execute the queries individually, but I'm having trouble combining them.

SELECT RELATIVEPATH, COUNT(RELATIVEPATH) FROM APP 
       WHERE (FIELD IS NULL) OR (FIELD = '')
       GROUP BY (RELATIVEPATH);

SELECT RELATIVEPATH, COUNT(RELATIVEPATH) FROM APP
       WHERE (FIELD IS NOT NULL) 
       GROUP BY (RELATIVEPATH);

Upvotes: 1

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

You need the case statement:

SELECT RELATIVEPATH, sum(case when (FIELD IS NULL) OR (FIELD = '') then 1 else 0 end),
       sum(case when field is not null then 1 else 0 end)
FROM APP 
GROUP BY (RELATIVEPATH);

This is assuming that the intention of count(RelativePath) is to count all the rows. If you really want to count the non-NULL values of the column, you would use:

SELECT RELATIVEPATH, count(case when (FIELD IS NULL) OR (FIELD = '') then RelativePath end),
       count(case when field is not null then RelativePath end)
FROM APP 
GROUP BY (RELATIVEPATH);

Upvotes: 7

Related Questions