CODEWITHSUNDEEP
sqloracle-databasedate
San Antonio Aggie
San Antonio Aggie

Reputation: 11

Splitting Date Range by Month and Category

I have a table with the following columns: Reportdate, Category. 



Reportdate     Category
-------------------
01/01/2013     IT
01/01/2013     Grounds
01/01/2013     HVAC
01/03/2013     HVAC
02/01/2013     IT
02/02/2013     IT
02/02/2013     HVAC
02/02/2013     Grounds

I need a query that will output the following:



           Jan     Feb
---------------------------    
IT         1       2
Grounds    1       1
HVAC       2       1

If someone can help I would really appreciate it. Thanks

Upvotes: 1

Views: 38

Answers (2)

David Faber
David Faber

Reputation: 12486

You could use a PIVOT or you might try the following:

SELECT category
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 1, 1, 0)) AS "Jan"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 2, 1, 0)) AS "Feb"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 3, 1, 0)) AS "Mar"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 4, 1, 0)) AS "Apr"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 5, 1, 0)) AS "May"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 6, 1, 0)) AS "Jun"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 7, 1, 0)) AS "Jul"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 8, 1, 0)) AS "Aug"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 9, 1, 0)) AS "Sep"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 10, 1, 0)) AS "Oct"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 11, 1, 0)) AS "Nov"
     , SUM(DECODE(EXTRACT(MONTH FROM reportdate), 12, 1, 0)) AS "Dec"
  FROM mytable
 GROUP BY category

If you want to differentiate by year that could complicate things!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

A simple way to do this is with conditional aggregation:

select category,
       sum(case when extract(month from reportdate) = 1 then 1 else 0 end) as Jan,
       sum(case when extract(month from reportdate) = 2 then 1 else 0 end) as Feb
from table
group by category;

Upvotes: 1

Related Questions