Vijaykumar Vijaykumar - 6 months ago 14
SQL Question

How to group a field in table only untill it changes value (SQL Server)

I have the Following Table 'ProductTable'

Product | Series | Date
aaa | a1 | 2016-05-17 08:02:54.960
aaa | a1 | 2016-05-17 08:03:54.960
aaa | a1 | 2016-05-17 08:04:54.960
bbb | b1 | 2016-05-17 08:05:54.960
bbb | b1 | 2016-05-17 08:06:54.960
aaa | a1 | 2016-05-17 08:07:54.960
aaa | a1 | 2016-05-17 08:08:54.960


I tried few steps from google and found something called
row_number() over
but failed to get the exact results using it as I am new to SQL SERVER.I want the result to be like this -- >

Product | Series | Qty | Cumml.
aaa | a1 | 3 | 3
bbb | b1 | 2 | 5
aaa | a1 | 2 | 7


How to do grouping of this nature? Please Help ....

Answer

This is a standard islands solution with some window aggregate function for cumulative sum:

DECLARE @p TABLE
    (
      Product CHAR(3) ,
      Date DATETIME
    )
INSERT  INTO @p
VALUES  ( 'aaa', '2016-05-17 08:02:54.960' ),
        ( 'aaa', '2016-05-17 08:03:54.960' ),
        ( 'aaa', '2016-05-17 08:04:54.960' ),
        ( 'bbb', '2016-05-17 08:05:54.960' ),
        ( 'bbb', '2016-05-17 08:06:54.960' ),
        ( 'aaa', '2016-05-17 08:07:54.960' ),
        ( 'aaa', '2016-05-17 08:08:54.960' );
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY date )
                        - ROW_NUMBER() OVER ( PARTITION BY Product ORDER BY Date ) AS rn
               FROM     @p
             )
    SELECT  rn ,
            product ,
            COUNT(*) AS Cnt,
            SUM(COUNT(*)) OVER ( ORDER BY MAX(Date) ) AS Cumul
    FROM    cte
    GROUP BY rn ,
            product
    ORDER BY Cumul

Output:

rn  product Cnt Cumul
0   aaa     3   3
3   bbb     2   5
2   aaa     2   7
Comments