Oracle SQL Query -> Count 2 columns under 2 different conditions

Question

I have a table that looks like this:

YEAR        RESOLUTION_DATE      CREATION_DATE  

2013                             2013/02/18
2012                             2012/05/26
2009                             2009/11/11
2013          2013/12/08         2013/12/01
2000                             2000/17/31
2007                             2007/12/08
2012                             2012/12/08
2012          2012/03/23         2012/03/10 
2012                             2012/12/08
2007                             2007/01/17
2012          2012/01/17         2012/01/10
2009                             2009/02/14

I am trying to make a query that will output the following:

YEAR      COUNT_RESOLUTION_DATE      COUNT_CREATION_DATE  
2000               0                          1
2007               0                          2
2009               0                          2
2011               0                          0
2012               2                          5
2013               1                          2

The caveat is that I would like the query to count the number of RESOLUTION_DATE by YEAR, where the RESOLUTION_DATE IS NOT NULL and i want to count ALL CREATION_DATE's. The SQL is needed for an oracle database.

Why is count_creation_date 0 for the year 2000? Shouldn't it be 1 since there is a row with a year of 2000 (and a creation_date of 2008-- that seems odd but I don't see why you wouldn't count it)? Why is the count_creation_date for 2011 23? I only see 1 row with a year of 2011 (and that somehow has a creation_date of 2007). — Justin Cave
– Justin Cave, Commented Feb 12, 2013 at 21:59
I created dummy numbers initally. I have now updated them...they should make sense now. My apologies. — JT2013
– JT2013, Commented Feb 13, 2013 at 13:46

Bulat · Accepted Answer · 2013-02-12 22:05:24Z

4

Try this:

SELECT 
COUNT(RESOLUTION_DATE) AS COUNT_RESOLUTION_DATE, 
COUNT(CREATION_DATE) AS COUNT_CREATION_DATE
FROM MyTable
GROUP BY YEAR
ORDER BY YEAR

edited Feb 12, 2013 at 22:05

answered Feb 12, 2013 at 21:53

Bulat

6,9992 gold badges32 silver badges54 bronze badges

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

2 Comments

JT2013 Over a year ago

Hi, but i only want to count the resolution_date where is it not null. and i want to count ALL creation_date's.

Xophmeister Over a year ago

count skips null values.

Ken Keenan · Accepted Answer · 2013-02-13 14:03:04Z

3

If you only want the non-NULL resolution dates counted, this should work:

SELECT 
SUM(CASE WHEN RESOLUTION_DATE IS NULL THEN 0 ELSE 1 END) AS COUNT_RESOLUTION_DATE, 
COUNT(CREATION_DATE) AS COUNT_CREATION_DATE
FROM MyTable
GROUP BY YEAR
ORDER BY YEAR;

answered Feb 13, 2013 at 14:03

Ken Keenan

10.7k5 gold badges35 silver badges52 bronze badges

1 Comment

JT2013 Over a year ago

Worked like a charm! I realized that COUNT will always skip null values...awesome Ken!

Collectives™ on Stack Overflow

Oracle SQL Query -> Count 2 columns under 2 different conditions

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related