Clever Geek Handbook

Oracle SQL Query & # 8594; Count 2 columns in two different conditions

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'm 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 RESOLUTION_DATE number using YEAR, where RESOLUTION_DATE is NOT NULL and I want to count ALL CREATION_DATE. SQL is required for oracle database. In the meantime, there is no need to know about it. ”

+3


source to share


2 answers


If you want non-NULL dates to be 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;
+3


source


Try the following:



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


source






More articles:


All Articles