GROUP BY in range of tens
-
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:
UserA 1 GROUP A
UserB 5 GROUP A
UserC 10 GROUP A
UserD 11 GROUP B
UserE 15 GROUP B
UserF 20 GROUP B
UserG 21 GROUP C
UserH 25 GROUP C
UserI 30 GROUP C
UserJ 41 GROUP D
UserK 45 GROUP D
UserL 50 GROUP 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!!
-
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 ?
-
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...
-
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
<AnyNumber>-1 / 10 = <The Group>
-
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..
-
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/
-
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
-
I suggest that you do some performance testing with this query.
- arjun
https://sqlroadie.com/
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply