OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Group by

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group by [message #23681]

Thu, 26 December 2002 13:35

Sam
Messages: 255
Registered: April 2000

Senior Member

Hi All,
I have a small doubt on group by, please anyone help me
I have the query as follows

select id,to_char(last_day(event_date),'MON') Month,count(*) from table1
where to_char(event_date,'YYYY')='2002'
group by id,to_char(last_day(event_date),'MON');

this gives me the output like
feb 30
dec 20
I need the output as follows
jan 0
feb 30
mar 0
........... and so on.
can anyone help me on this?

Report message to a moderator

Re: Group by [message #23682 is a reply to message #23681]

Thu, 26 December 2002 13:40

ann
Messages: 24
Registered: August 2000

Junior Member

After the group by: group by id,to_char(last_day(event_date),'MON')
order by id

Ann

Report message to a moderator

Re: Group by [message #23686 is a reply to message #23681]

Fri, 27 December 2002 01:01

Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA

Senior Member

With a slight modification to Andy's suggestion, you can display the months as JAN, FEB, etc. and still keep them in the proper order with JAN first and DEC last:

select x.id, TO_CHAR (TO_DATE (x.month, 'MM'), 'MON'), sum(x.rcnt) as rec_cnt
from   ((select   distinct t1.id, z.month, 0 rcnt
         from     table1 t1,
                  (select '01' month from dual union all
                   select '02' month from dual union all
                   select '03' month from dual union all
                   select '04' month from dual union all
                   select '05' month from dual union all
                   select '06' month from dual union all
                   select '07' month from dual union all
                   select '08' month from dual union all
                   select '09' month from dual union all
                   select '10' month from dual union all
                   select '11' month from dual union all
                   select '12' month from dual) z)
         union all
         select   t2.id, to_char(t2.event_date,'MM') month, count(*) rcnt
         from     table1 t2
         where    to_char(t2.event_date,'YYYY')='2002'
         group by t2.id, to_char(t2.event_date,'MM')) x
group by x.id, x.month
/

Report message to a moderator

Re: Group by [message #23688 is a reply to message #23681]

Fri, 27 December 2002 14:24

sri
Messages: 154
Registered: February 2000

Senior Member

Alternative here,

SELECT id, mon, cnt FROM
(select id, to_char(event_date, 'MON') mon, count(*) cnt from table1
group by id, to_char(event_date, 'MON')
union all
select a.id, b.mon, 0 cnt from
(select distinct id from table1) a,
(select to_char(add_months(to_date('01-JAN-02'), (rownum - 1)), 'MON') mon
from table1 where rownum <= 12) b
where b.mon not in (select to_char(event_date, 'MON') from table1))
ORDER BY id, to_date(mon, 'MM')
/

Thx,
SriDHAR

Report message to a moderator

Previous Topic:	Looking for "Elegant" Solution
Next Topic:	CONNECT VB WITH ORACLE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 19:15:39 CDT 2024