SQL COUNT - Output table with two COUNT columns with differing WHERE clauses

Question

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!

score 8 · Accepted Answer · answered Jun 11 '12 at 14:47

8

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.

answered Jun 11 '12 at 14:47

4

@Sebas - This 'trick' also allows you to do things like `COUNT(DISTINCT CASE WHEN x THEN some_id ELSE NULL END)`. It's a lovely little tool. – MatBailie Jun 11 '12 at 15:07
1

yeah, neat :-) I guess it is transportable to mysql as well! yay! – Sebas Jun 11 '12 at 15:09
3

By the way, the `else null` part isn't needed since null is the default if not supplied – Mike Meyers Jun 11 '12 at 15:52

score 4 · Answer 2 · answered Jun 11 '12 at 14:58

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

score 1 · Answer 3 · edited Jun 11 '12 at 14:55

1

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>

edited Jun 11 '12 at 14:55

Ash Burlaczenko

24,778
15
68
99

answered Jun 11 '12 at 14:48

Jason Coyne

6,509
8
40
70

SQL COUNT - Output table with two COUNT columns with differing WHERE clauses

3 Answers3