CODEWITHSUNDEEP
sqloracle-databasecountoracle-apex
Spags
Spags

Reputation: 541

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!

Upvotes: 4

Views: 1932

Answers (3)

Dave Costa
Dave Costa

Reputation: 48121

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

Upvotes: 4

Jason Coyne
Jason Coyne

Reputation: 6636

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>

Upvotes: 1

user554546
user554546

Reputation:

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.

Upvotes: 8

Related Questions