GROUP BY in range of tens

Question

Post reply

GROUP BY in range of tens

ColdCoffee

SSC-Dedicated

Points: 39972
More actions
April 1, 2010 at 1:15 am

#213346

Hi people.
I have a requirement. I need to group the data into groups of tens.
Issue:
I have cost data. It wil have varied values. Now i need to mark the data like, if the cost lies between 0 to 10, then its GROUP 1, if cost between 11 to 20 it is GRUOP 2 and so on...
Any one knows how to do it.
I am currently working on it with a temp table and an join query. i will give out the query in a while
Table Structure
if object_id('tempdb..#Costs') is not null
drop table #Costs
Create table #Costs
(
UserName Varchar(20),
Cost Int
)
Sample Data
insert into #Costs
select 'UserA', 1
Union All
select 'UserB', 5
Union All
select 'UserC', 10
UNion All
select 'UserD', 11
Union All
select 'UserE', 15
Union All
select 'UserF', 20
UNion All
select 'UserG', 21
Union All
select 'UserH', 25
Union All
select 'UserI', 30
UNion All
select 'UserJ', 41
Union All
select 'UserK', 45
Union All
select 'UserL', 50
Desired Output:
UserA1GROUP A
UserB5GROUP A
UserC10GROUP A
UserD11GROUP B
UserE15GROUP B
UserF20GROUP B
UserG21GROUP C
UserH25GROUP C
UserI30GROUP C
UserJ41GROUP D
UserK45GROUP D
UserL50GROUP D
Constraints and Liberties:
1. i dont know the max cost of the table, so we must obtain the max value for the cost from the table and then find out the upper limit of the last range.
Example : say the max value in the cost column is 72, then the upper range limit become 71 - 80 and say GROUP X will be allocated to taht..
2. Not necessarily the last column in Desired output must be GROUP A. It may be GROUP 1, GROUP 2 etc etc or RANGE 1,RANGE 2 etc etc..
3. U can create temp tables to hold the range/group values, performance is not the top priority 🙂
Any help will be appreciated guys. For ur convenience, i have attached a visual image of my desired output.
Please tell me if u guys need more info!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Im quite confused by your requirements , how is this not easily achieved by dividing the (cost-1) by 10 ?

re 2: How should the query know to show RANGE or GROUP ?

Clear Sky SQL
My Blog[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Dave, i guess the sample data made confusion.. the cost can be anything, say 1, 4, 7, 11, 13, 12, 22,27, 75,43 etc etc any integer number..

for your second question, its upto us..we can have the name as range or group..

hope i clarified your doubt...

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

COldCoffee (4/1/2010)
Dave, i guess the sample data made confusion.. the cost can be anything, say 1, 4, 7, 11, 13, 12, 22,27, 75,43 etc etc any integer number..

Still not seeing the difficulty....

So :

1-1 /10 = 0

4-1/10 = 0

7-1/10 =0

10-1/10 = 0

11-1/10 = 1

12-1/10 = 1

20-1/10 = 1

21-1/10 = 2

22-1/10 = 2

75-1/10 = 7

43-1/10 = 4

or

Clear Sky SQL
My Blog[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Oh dave, how naive i have been.. the bug-pressure banded to me to do some astronimical calculations...thanks man.. thanks a lot..i did not even think in that line..

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 5

Check this out but it is not simpler than Dave's solution. There are no assumptions here. I assume that if you have a group classification, you would like a Group table in your DB.

Create table #Costs(UserName Varchar(20),Cost Int)

insert into #Costs

select 'UserA', 1

Union All

select 'UserB', 5

Union All

select 'UserC', 10

UNion All

select 'UserD', 11

Union All

select 'UserE', 15

Union All

select 'UserF', 20

UNion All

select 'UserG', 21

Union All

select 'UserH', 25

Union All

select 'UserI', 30

UNion All

select 'UserJ', 41

Union All

select 'UserK', 45

Union All

select 'UserL', 50

create table #Groups(lowerlimit smallint, upperlimit smallint, GName varchar(10))

insert into #Groups values(0,10,'GroupA')

insert into #Groups values(11,20,'GroupB')

insert into #Groups values(21,30,'GroupC')

insert into #Groups values(31,40,'GroupD')

insert into #Groups values(41,50,'GroupE')

select *

from

#Costs c

join #Groups g

on c.cost >= g.lowerlimit and c.cost <= g.upperlimit

drop table #costs

drop table #groups

https://sqlroadie.com/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Thanks Arjun for an aliter code 🙂

Infact, i used a temporary table to old the data ranges and then joined it to get the ranges, much like your code 🙂

Dave's solution was the simplest., so i adopted it and formed the code.

Here is the final solution (customized for my bug though :-))

;WITH User_Count(Group_Number , COUNT_USERS )

AS

(

SELECT

FLOOR((((Cost - 1.00) / 10.00))+ 1.00) AS Group_Number

, COUNT(*) AS COUNT_USERS

FROM #Costs

GROUP BY

FLOOR((((Cost - 1.00) / 10.00))+ 1.00)

)

SELECT

CASE

WHEN (((Group_Number - 1) * 10)+1) = 1

THEN CAST (((Group_Number - 1) * 10) AS VARCHAR(1024)) + ' To ' + CAST ( (Group_Number * 10) AS VARCHAR(1024))

ELSE CAST ( ((Group_Number - 1) * 10)+1 AS VARCHAR(1024)) + ' To ' + CAST ( (Group_Number * 10) AS VARCHAR(1024))

END Group_Number

, COUNT_USERS

FROM User_Count

ORDER BY

((Group_Number - 1) * 10)+1

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 7

I suggest that you do some performance testing with this query.

- arjun

https://sqlroadie.com/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

yes Arjun, i have to tweak ti for performance now.. i have added lot of garbage in that.. should clean the code.. once done, i wil post the optimized query.. thanks for the suggestion