CODEWITHSUNDEEP
sqloracle-databasedatetime
misguided
misguided

Reputation: 3789

Group By datetime fields

I have a table which contains ID field and 3 datetime fields. I need get the count of each date time field monthwise.I am using 

Select to_char(datatime1,'Mon-yyyy'),count(id) from table
where datetime1 is not null

Select to_char(datatime2,'Mon-yyyy'),count(id) from table
where datetime2 is not null

Select to_char(datatime3,'Mon-yyyy'),count(id) from table
where datetime3 is not null

Then I am copying this results onto excel and setting up data as below :

Month     CountID(datetime1) CountID(datetime2) CountID(datetime3)
June-2013  50                   20                   16
July-2013  24                   10                   56

I was wondering if there is a way I can combine the three queries , instead of writing once query at a time?

Upvotes: 2

Views: 120

Answers (1)

Nicholas Carey
Nicholas Carey

Reputation: 74197

Try

select p , sum(cnt1) as cnt1 , sum(cnt2) as cnt1 , sum( cnt3 ) as cnt3
from ( select to_char(datetime1,'Mon-yyyy') as p    ,
              count(id)                     as cnt1 ,
              0                             as cnt2 ,
              0                             as cnt3
       from table
       where datetime1 is not null
       group by to_char(datetime1,'Mon-yyyy')
       UNION ALL
       select to_char(datetime2,'Mon-yyyy') as p    ,
              0                             as cnt1 ,
              count(id)                     as cnt2 ,
              0                             as cnt3
       from table
       where datetime2 is not null
       group by to_char(datetime2,'Mon-yyyy')
       UNION ALL
       select to_char(datetime3,'Mon-yyyy') as p    ,
              0                             as cnt1 ,
              0                             as cnt2 ,
              count(id)                     as cnt3  
       from table
       where datetime2 is not null
       group by to_char(datetime2,'Mon-yyyy')
     ) t
group by t.p
order by t.p

Or the arguably simpler union + left join:

select to_char(t.dt,'Mon-yyyy') as period ,
       sum(case when t1.id is not null then 1 else 0 end) as cnt1 ,
       sum(case when t2.id is not null then 1 else 0 end) as cnt2 ,
       sum(case when t3.id is not null then 1 else 0 end) as cnt3
from ( select datetime1 as dt from table where datetime1 is not null
       UNION
       select datetime2 as dt from table where datetime2 is not null
       UNION
       select datetime3 as dt from table where datetime3 is not null
     ) t
left join table t1 on t1.datetime1 = t.dt
left join table t2 on t2.datetime2 = t.dt
left join table t3 on t3.datetime3 = t.dt
group by to_char(t.dt,'Mon-yyyy')

There's more than one way to do it.

Upvotes: 1

Related Questions