Ask Question
Help Others
Home › Forums › Database › SQL & PL/SQL

You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Creating Groups

  • Post reply
1 reply [Last post]
Thu, 03/20/2008 - 9:45pm
Guest's picture
Guest

I would like to use a GROUP BY statement ( such as GROUP BY month) but instead of having 12 months that are being grouped by I would like to put january and Febuary in a group and march, april, may, june, july in a group and the rest in another group or something like this ( make my own groups using one of the columns). Is this a simple statement? I have thought about adding another column, but thought there might be an easier way without having to add a seperate column. I would like to also change the grouping easily without deleting columns and recreating new ones.

Thanks for any insight you my have on this.

Aloha
Jeremy

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Reddit
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
Top
Thu, 03/20/2008 - 9:48pm
#1
CompShack's picture
CompShack
User offline. Last seen 41 weeks 2 days ago. Offline
Joined: 12/09/2007
Posts: 167
Use Decode Function

Hi Jeremy,
No you don't need to create any new columns. You can use the decode function in your SELECT to accomplish what you wrote above.

Here is a simple example:

SELECT DECODE(UPPER(month),
  'JAN', 'GROUP1',
  'FEB', 'GROUP1',
  'MAR', 'GROUP2',  
  'APR', 'GROUP2',  
  'MAY', 'GROUP2',  
  'JUN', 'GROUP2',  
  'JUL', 'GROUP2',  
  'AUG', 'GROUP3',  
  'SEP' , 'GROUP3',  
  'OCT',  'GROUP3',  
  'NOV', 'GROUP3',  
  'DEC', 'GROUP3') AS month FROM table1
GROUP BY DECODE(DECODE(UPPER(month),
  'JAN', 'GROUP1',
  'FEB', 'GROUP1',
  'MAR', 'GROUP2',  
  'APR', 'GROUP2',  
  'MAY', 'GROUP2',  
  'JUN', 'GROUP2',  
  'JUL', 'GROUP2',  
  'AUG', 'GROUP3',  
  'SEP' , 'GROUP3',  
  'OCT',  'GROUP3',  
  'NOV', 'GROUP3',  
  'DEC', 'GROUP3');

This Should Work.

n/a
Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
2 + 9 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.