CODEWITHSUNDEEP
sqlgroup-by
Stan Darsh
Stan Darsh

Reputation: 43

SQL: GROUP BY for number ranges?

I'm a first time poster and new to SQL, as are my colleagues.
I have a column of numbers (let's just call it "numbers") and the values in it range from 1-9999.
I need to create a grouping column (let's call it "numbers level") and assign the values of AA to all numbers from 1-999, BB from 1000-6999 and CC for 7000-9999.
So, the final output would look like below (output does not need to be ordered):

numbers      |   numbers level
-------------------------------------
   136       |        AA
  2935       |        BB
  4288       |        BB
  8987       |        CC
  436        |        AA
  3737       |        BB

Is there any easy way to script this, i.e.: by using a GROUP BY clause?
Or does that grouping column have to exist already and I need to just populate it with a command?

Please let me know if my question wreaks of cluelessness and I'll clarify the best I can.

Upvotes: 2

Views: 18085

Answers (3)

GolezTrol
GolezTrol

Reputation: 116100

The grouping column has to exist, but you could use a case instead.

select nr,
       case when nr <= 999 then 'AA'
            when nr <= 7000 then 'BB'
       else 'CC'
       end as NrLevel
from   Numbers

Upvotes: 3

Gene
Gene

Reputation: 392

You don't really need a group here, you can accomplish it using a simple case statement.

SELECT number,
CASE WHEN number between 1 and 999 THEN 'AA'
     WHEN number between 1000 and 6999 THEN 'BB'
     WHEN number between 7000 and 9999 then 'CC'
     END numbers_level
from Number_table

Upvotes: 1

APH
APH

Reputation: 4154

This would be easiest with a case statement (assuming you're using sql-server; if not, please add tags for the correct version).

Select [Numbers]
  , case when [Numbers] between 1 and 999 then 'AA'
         when [Numbers] between 1000 and 6999 then 'BB'
         when [Numbers] between 7000 and 9999 then 'CC'
    end as [Numbers Level]
from MyTable

If any of your numbers don't fall into those ranges, it will return NULL - use else if you want a different result when this happens.

Upvotes: 12

Related Questions