If you use "decode" you can specify the "group by" field very precisely.
Example:
select decode(substr(to_char(active_date), 4, 3),
'JAN', '1st Qtr',
'FEB', '1st Qtr',
'MAR', '1st Qtr',
'APR', '2nd Qtr',
'MAY', '2nd Qtr',
'JUN', '2nd Qtr',
'JUL', '3rd Qtr',
'AUG', '3rd Qtr',
'SEP', '3rd Qtr',
'OCT', '4th Qtr',
'NOV', '4th Qtr',
'DEC', '4th Qtr'), count(active_date)
from some_table
group by decode(substr(to_char(active_date), 4, 3),
'JAN', '1st Qtr',
'FEB', '1st Qtr',
'MAR', '1st Qtr',
'APR', '2nd Qtr',
'MAY', '2nd Qtr',
'JUN', '2nd Qtr',
'JUL', '3rd Qtr',
'AUG', '3rd Qtr',
'SEP', '3rd Qtr',
'OCT', '4th Qtr',
'NOV', '4th Qtr',
'DEC', '4th Qtr')
Unfortunately you have to repeat the "decode" statement in the "group by"
expression (not the case if you use "order by", then you can specify a field
alias or the field number).
Hope this helps.
Tim Helck
-----Original Message-----
From: Fong, Anna [mailto:anna@;water.ca.gov]
Sent: Thursday, November 14, 2002 3:01 PM
To: [EMAIL PROTECTED]
Subject: OT: Oracle Group By statement
Can anyone point me to the answer?
I want to use an arbitrary group in the group by statement. I can group by
year but want to change the calendar year to another unit of measure such as
quarter.
This works:
select to_char(actual_date,'YYYY'), sum(value) from daily_data
where sensor_id=?
group by to_char(actual_date,'YYYY')
Any ideas?
--------------------------------------------------------
Anna Q. Fong, Webmaster
California Data Exchange Center
