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

1 reply [Last post]
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

CompShack's picture
User offline. Last seen 7 years 17 weeks 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