CODEWITHSUNDEEP
sql
Leon
Leon

Reputation: 581

Splitting one column in a SQL query into multiple columns

The idea is this, I have one column that has values that range from 0 to 25,000.

I want to basically have one column that counts the number of values from 0 to 5000, 5000-10000, etc.

Where would I start? And how would I do such a thing?

Upvotes: 0

Views: 116

Answers (1)

shawnt00
shawnt00

Reputation: 17915

I'm assuming you don't really want the ranges to overlap:

count(case when <column> between     0 and  5000 then 1 else null end) as range0,
count(case when <column> between  5001 and 10000 then 1 else null end) as range1,
...

or maybe you prefer (if you actually meant 0 to 4999, 5000 to 9999, etc):

count(case when <column> / 5000 = 0 then 1 else null end) as range0,
count(case when <column> / 5000 = 1 then 1 else null end) as range1,
count(case when <column> / 5000 = 2 then 1 else null end) as range2,
count(case when <column> / 5000 = 3 then 1 else null end) as range3,
count(case when <column> / 5000 = 4 then 1 else null end) as range4,
count(case when <column> / 5000 = 5 then 1 else null end) as range5

You'd need the last one to actually cover 25,000 or just do >= 4. And note that I assumed integer division.

Upvotes: 2

Related Questions