Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question 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:

    Top 5 Spreads $695,066
    Next 5 Spreads $467,845
    Next 5 Spreads $416,946
    Next 5 Spreads $361,946
    Next 5 Spreads $305,607
    Next 5 Spreads $270,567
    Bottom Spreads $15,954

    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. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Try NTile(5) instead of Row_Number.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by LinksUp View Post
    Try NTile(5) instead of Row_Number.
    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. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    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. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by clawlan View Post
    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. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by LinksUp View Post
    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. #7
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163

    Post

    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. #8
    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. #9
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by LinksUp View Post
    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. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  11. #11
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by imex View Post
    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. #12
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by imex View Post
    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. #13
    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. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It works because when SQL Server divides an integer datatype by an integer datatype, the result is also an integer datatype, meaning that fractional remainders are truncated.
    So: 0/5=0, 1/5=0, 2/5=0, 3/5=0, 4/5=0, and then 5/5=1.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •