query with multiple count statements

Question

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);

Gordon Linoff · Accepted Answer · 2012-12-17 18:46:04Z

7

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);

answered Dec 17, 2012 at 18:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MikeSmithDev Over a year ago

Probably need an "AND FIELD <> '' " in that 2nd CASE statement.

Collectives™ on Stack Overflow

query with multiple count statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related