Home » SQL & PL/SQL » SQL & PL/SQL » group data by 6 months (10g)
group data by 6 months [message #420501] Sun, 30 August 2009 22:19 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Actually im performing mass update to a table, due to the size of the table, i plan to do it on incremental basis.

Issuing the following query allows me to group data by month-yy

select to_char(nvl(INVOICE_DATE,'31-Dec-9999'),'MON-YY'),count(*) 
from  deliver_org 
group by to_char(nvl(INVOICE_DATE,'31-Dec-9999'),'MON-YY')



Is there any way i could group by on an interval of six months, rather than monthly?

[Updated on: Sun, 30 August 2009 23:22] by Moderator

Report message to a moderator

Re: group data by 6 months [message #420502 is a reply to message #420501] Sun, 30 August 2009 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way i could group by on an interval of six months
How do you propose to represent a semi-annual interval in database terms?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Sun, 30 August 2009 23:04]

Report message to a moderator

Re: group data by 6 months [message #420506 is a reply to message #420501] Sun, 30 August 2009 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select add_months(sysdate,level-1) dt from dual connect by level <= 12)
  2  select dt, 
  3         extract (year from dt) yr,
  4         trunc((to_number(to_char(dt,'Q'))-1)/2) sem 
  5  from data
  6  /
DT                  YR        SEM
----------- ---------- ----------
31-AUG-2009       2009          1
30-SEP-2009       2009          1
31-OCT-2009       2009          1
30-NOV-2009       2009          1
31-DEC-2009       2009          1
31-JAN-2010       2010          0
28-FEB-2010       2010          0
31-MAR-2010       2010          0
30-APR-2010       2010          0
31-MAY-2010       2010          0
30-JUN-2010       2010          0
31-JUL-2010       2010          1

12 rows selected.

Regards
Michel
Re: group data by 6 months [message #420508 is a reply to message #420506] Mon, 31 August 2009 00:12 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thanks guys, i will take note of the sql/points above
Previous Topic: How to insert records in Mutilevel nested table
Next Topic: search for a column value in all tables of a schema
Goto Forum:
  


Current Time: Mon Nov 04 11:01:28 CST 2024