SQL COUNT - output table with two COUNT columns with different WHERE clauses

Question

I need to create a report in Oracle APEX, which 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 generated by the SQL COUNT . The requested table is as follows:

  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... 

Therefore, in one query, I need to count on OPEN_ISSUE_COUNT and CLOSED_ISSUE_COUNT , where ISSUS_STATUS = 'OPEN' and ISSUS_STATUS = 'CLOSED' respectively, and GROUP BY ASSOCIATED_PROJECT .

It makes sense? Obviously, I can easily do this for one of two statuses, but I could not come up with any viable solution for what I am describing here. I looked at some things here and elsewhere on the Internet and did not see anything like it. Let me know what you guys think. Thanks!

Answer 1

Since count() only considers non-zero 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 is not the case — and if you want these other statuses to be calculated — then configure the query accordingly.

Answer 2

Another way to do this with the new PIVOT function:

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

Answer 3

 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>