Home » SQL & PL/SQL » SQL & PL/SQL » Grouping
Grouping [message #226465] Fri, 23 March 2007 21:10 Go to next message
Lyndey
Messages: 11
Registered: November 2005
Location: Cambridge, MA
Junior Member
Hi:

I'm working on a query that will eventually become part of a larger project, but I can't seem to get the basics working. I'm trying to group and order records in 0 to 3 month and 4 to 6 month groups. Here's what I have so far:

case when grouping (das_LAST_DATE >=add_months('7-Jan-2007', -3))=1 then '0_to_3'
when grouping(das_LAST_DATE <add_months('7-JAN-2007',-3))
and (das_LAST_DATE >= add_months('7-JAN-2007', -6)) =1 then '3_to_6'
end
from the_table
group by das_last_date
order by das_last_date


I keep receiving an error that I'm missing a right parenthesis, but I can't seem to find what I'm doing wrong.

Any and all help would be appreciated.

Thanks!
Re: Grouping [message #226466 is a reply to message #226465] Fri, 23 March 2007 21:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Any and all help would be appreciated.
Why do you expect meaningful help when part of the problem is withheld?
By my count the parenthesis posted match up; therefore I can only conclude the actual problem is in the code not provided.
Solutions happen faster when you simply do CUT & PASTE the whole interaction with Oracle rather than describe what you think is or is not happening.
Re: Grouping [message #226479 is a reply to message #226466] Sat, 24 March 2007 00:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Before using GROUPING function, you should read about it in the documentation.
It is not correctly used as you did not specify ROLLUP or CUBE in the GROUP BY section.
However, in my opinion, it is not usable for your requirement. Maybe you will need to use it later for the query enhancement you did not describe here.

There are many articles about your requirement on net. Read this article on AskTom to get the idea, or search anywhere for the word 'pivot'.
Re: Grouping [message #226493 is a reply to message #226465] Sat, 24 March 2007 02:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
add_months('7-Jan-2007', -3)

add_months takes a date as first parameter, not a string.
Re: Grouping [message #226501 is a reply to message #226465] Sat, 24 March 2007 04:45 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Or, do you want something like this (untested):
SELECT CASE 
         WHEN months_between (to_date( '7-Jan-2007', 'DD-Mon-YYYY'), das_last_date) < 3 THEN '0_to_3'
         WHEN months_between (to_date( '7-Jan-2007', 'DD-Mon-YYYY'), das_last_date) >= 3 
          AND months_between (to_date( '7-Jan-2007', 'DD-Mon-YYYY'), das_last_date) < 6 THEN '3_to_6'
       END month_group, count(*)
FROM the_table
GROUP BY CASE
           WHEN months_between (to_date( '7-Jan-2007', 'DD-Mon-YYYY'), das_last_date) < 3 THEN '0_to_3'
           WHEN months_between (to_date( '7-Jan-2007', 'DD-Mon-YYYY'), das_last_date) >= 3 
            AND months_between (to_date( '7-Jan-2007', 'DD-Mon-YYYY'), das_last_date) < 6 THEN '3_to_6'
         END
ORDER BY 1;
Previous Topic: searching for a string in a string
Next Topic: give privilages to users
Goto Forum:
  


Current Time: Thu Dec 08 03:52:55 CST 2016

Total time taken to generate the page: 0.08203 seconds