SQL COUNT - Output table with two COUNT columns with differing WHERE clauses
I have the need to generate a report in Oracle APEX that is similar to the example below:
PROJECT OPEN_ISSUE_COUNT CLOSED_ISSUE_COUNT W-1 3 1 X-2 1 2 Y-3 5 3 Z-4 2 1
Where OPEN_ISSUE_COUNT and CLOSED_ISSUE_COUNT are generated by a SQL COUNT statement. The table being queried looks like this:
ISSUE_# ISSUE_STATUS ASSOCIATED_PROJECT 1A OPEN W-1 1B OPEN W-1 1C OPEN W-1 2A CLOSED W-1 2B OPEN X-2 2C CLOSED X-2 3A CLOSED X-2 etc...
So in one query I need to count for OPEN_ISSUE_COUNT and CLOSED_ISSUE_COUNT where ISSUS_STATUS = 'OPEN' and ISSUS_STATUS = 'CLOSED' respectively and GROUP BY ASSOCIATED_PROJECT.
Does that make sense? Obviously I can easily do this for one of the two statuses, but have been unable to come up with any viable solution for what I am describing here. I have looked over some stuff here and elsewhere online and did not see something similar. Let me know what you guys think. Thanks!
Since count() only counts non-null values, this should work:
select associated_project as project, count(case when issue_status='OPEN' then 1 else null end) as open_issue_count, count(case when issue_status='CLOSED' then 1 else null end) as closed_issue_count from table group by associated_project;
Of course, this assumes that the only valid values for issue_status are 'OPEN' AND 'CLOSED'. If this isn't the case--and if you want those other statuses counted--then adjust the query accordingly.
Another way to do it is with the new PIVOT feature:
with issue_data as ( select associated_project as project, issue_status from issues ) select project, open, closed from issue_data pivot ( count(*) for issue_status in ('OPEN' as open, 'CLOSED' as closed) )
select sum(case when status = 'open' then 1 else 0 end) as open, sum(case when status = 'closed' then 1 else 0 end) as closed from table where <other restrictions>