# Thread: Grouping results into sets of 5?

1. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213

## Unanswered: Grouping results into sets of 5?

I have been tasked with creating a report that shows sales of our products grouped into buckets of 5 each, DESC. I have a table that has the itemNo and revenue. The final report would be something like:

My initial thinking was to use row_number() and partition to label the rows in groups of 5 like:

item 1 30,000 1
item 2 29,000 1
item 3 28,000 1
item 4 27,000 1
item 5 26,000 1
item 6 25,000 2
item 7 24,000 2
item 8 23,000 2
item 9 22,000 2
item 10 21,000 2
item 11 20,000 3
item 12 19,000 3
item 13 18,000 3

And then Sum the revenue, grouping by this row_number. But I haven't been able to get it working right. Any other ideas? Maybe I'm on the wrong track. Anyone have other ideas?

2. Registered User
Join Date
Dec 2012
Location
Logan, Utah
Posts
163

3. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Never used NTILE(X) before, thanks for bringing it to my attention. Unfortunately, it looking like this breaks down the dataset into X sets rather than breaking down the dataset into sets OF X.

4. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
I did manage to get this working with a WHILE loop, but unfortunately, I need to embed this query into an excel doc, which doesn't support this.

Code:
```declare @counter int
set @counter = 0
while (select count(*) from #table1) > 0
begin
set @counter = @counter + 1
insert into #table2
select top 5 *,@counter as ranking from #table1 Order by revenue desc
delete from #table1 where itemNo in(select itemNo from #table2)
end```

5. Registered User
Join Date
Dec 2012
Location
Logan, Utah
Posts
163
Originally Posted by clawlan
Unfortunately, it looking like this breaks down the dataset into X sets rather than breaking down the dataset into sets OF X.
Do you know or can you determine how many records you will be processing? If so, then X = RecordCount / 5 should create the required 5 records per grouping that you need!

6. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Do you know or can you determine how many records you will be processing? If so, then X = RecordCount / 5 should create the required 5 records per grouping that you need!

Hm, i think this will only work if my record set is always exactly divisible by 5. For example, if there are 52 records, 52/5 = 10. Then if I use NTILE(10), it will be slightly off because of those 2 extra records being stuck into the groups.

7. Registered User
Join Date
Dec 2012
Location
Logan, Utah
Posts
163
If the Mod of the number of records is > 0, then round up to the next whole number. Then the odd number of records are in the last tile.

numTile = int(52/5)
if 52 % 5 > 0
numTiles += 1
end

Using any other method, how would you deal with the odd number?

8. Registered User
Join Date
Apr 2012
Posts
213
clawlan, try something like this:

Code:
```select Rk, sum(MyValue) as TotValue
from (select (ROW_NUMBER() OVER(ORDER BY MyValue DESC) - 1) / 5 as Rk, MyValue
from MyTable) as t
group by Rk```
Hope it's useful.

9. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
If the Mod of the number of records is > 0, then round up to the next whole number. Then the odd number of records are in the last tile.

numTile = int(52/5)
if 52 % 5 > 0
numTiles += 1
end

Using any other method, how would you deal with the odd number?
thats not how ntile creates groups.

10. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
```DECLARE @dummy_data table (
when_created date
, price        money
)

INSERT INTO @dummy_data (when_created, price)
VALUES ('2010-01-01', 10)
, ('2010-02-01', 11)
, ('2010-03-01', 12)
, ('2010-04-01', 13)
, ('2010-05-01', 14)
, ('2010-06-01', 15)
, ('2010-07-01', 16)
, ('2010-08-01', 17)
, ('2010-09-01', 18)
, ('2010-10-01', 19)
, ('2010-11-01', 20)
, ('2010-12-01', 21)
, ('2011-01-01', 22)
, ('2011-02-01', 23)
, ('2011-03-01', 24)
, ('2011-04-01', 25)
, ('2011-05-01', 26)

; WITH x AS (
SELECT when_created
, price
, Row_Number() OVER (ORDER BY when_created) As row_num
FROM   @dummy_data
)
, y AS (
SELECT when_created
, price
, row_num
, Ceiling(row_num / 5.0) As grp
FROM   x
)
SELECT grp
, Sum(price)
FROM   y
GROUP
BY grp```

11. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Originally Posted by imex
clawlan, try something like this:

Code:
```select Rk, sum(MyValue) as TotValue
from (select (ROW_NUMBER() OVER(ORDER BY MyValue DESC) - 1) / 5 as Rk, MyValue
from MyTable) as t
group by Rk```
Hope it's useful.
this is excellent. thank you.

12. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Originally Posted by imex
clawlan, try something like this:

Code:
```select Rk, sum(MyValue) as TotValue
from (select (ROW_NUMBER() OVER(ORDER BY MyValue DESC) - 1) / 5 as Rk, MyValue
from MyTable) as t
group by Rk```
Hope it's useful.
Ok I am still trying to understand how/why this works. Can you help me understand?

13. Registered User
Join Date
Apr 2012
Posts
213
Try the script below:

Code:
```declare @MyTable table
(MyValue money);

insert into @MyTable values
(100),
(200),
(300),
(400),
(500),
(600),
(700),
(800),
(900),
(1000),
(1100),
(1200);

select
MyValue,
ROW_NUMBER() OVER(ORDER BY MyValue DESC) as Rn,
(ROW_NUMBER() OVER(ORDER BY MyValue DESC) - 1) / 5 as Rk
from @MyTable```
After that just group the results of this query by column Rk.

Hope this helps.

14. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595