Count before and Group together

Question

I would like someone to provide me a suggestion.

Let's say I got data like this:

    Ref No      Whatever      Seq      Value
    A           Bla bla…      1        1 
    A           Bla bla…      2        1
    A           Bla bla…      3        0
    A           Bla bla…      4        1
    B           Bla bla…      5        0
    B           Bla bla…      6        1
    B           Bla bla…      7        0
    B           Bla bla…      8        1
    B           Bla bla…      9        0

How could I use SQL to get something like this?

    Ref No      Whatever      Value      Total
    A           Bla bla…      0          2
    B           Bla bla…      0          0
    B           Bla bla…      0          1
    B           Bla bla…      0          1

Basically try to filter based on Value = 0 and to count the no of records before that based on sorting of Seq.

Thank you.

Answer 1

You may try this:

WITH
    [CTE1] AS
    (
        SELECT [Seq] AS [Val0Seq], [RefNo], [Seq], [Value]
        FROM @Data
        WHERE [Value] = 0
    UNION ALL
        SELECT C.[Val0Seq], D.[RefNo], D.[Seq], D.[Value]
        FROM @Data AS D INNER JOIN [CTE1] AS C ON C.[RefNo] = D.[RefNo] AND C.[Seq] = D.[Seq] + 1
        WHERE D.[Value] <> 0
    ),
    [CTE2] AS
    (
        SELECT MAX([Seq]) AS [Seq], COUNT(*) - 1 AS [Count]
        FROM [CTE1]
        GROUP BY [Val0Seq]
    )
SELECT D.[RefNo], D.[Whatever], D.[Value], C.[Count]
FROM @Data AS D INNER JOIN [CTE2] AS C ON C.[Seq] = D.[Seq]

The first common table expression ([CTE1]) is recursive. I will explain both parts separately.

The anchor statement (the SELECT before the UNION ALL) of [CTE1] selects all records that have value 0. That's my starting point. I assign a unique value [Val0Seq] to that record, corresponding with the [Seq] value. This will result in the following:

Val0Seq      Ref No      Seq      Value
3            A           3        0
5            B           5        0
7            B           7        0
9            B           9        0

The recursive statement (the SELECT after the UNION ALL) of [CTE1] keeps selecting all records before the already found records in the CTE as long as their value is not 0 and their [RefNo] values are equal. The entire result of CTE1 will be the following:

Val0Seq      Ref No      Seq      Value
3            A           1        1
3            A           2        1
3            A           3        0
5            B           5        0
7            B           6        1
7            B           7        0
9            B           8        1
9            B           9        0

The second common table expression ([CTE2]) groups the data of [CTE1] by [Val0Seq], since those groups contain the information I need. Per group, I select the largest [Seq] value (since that was the starting record where the value was 0) and the number of records in the group minus 1 (since I do not want to include that starting record in the count results). This logic of [CTE2] results in the following data:

[Seq]      [Count]
3          2
5          0
7          1
9          1

The final (main) query just joins the results of [CTE2] back to the original data (on field [Seq]) and shows the final results:

RefNo      Whatever      Value      Count
A          Bla bla...    0          2
B          Bla bla...    0          0
B          Bla bla...    0          1
B          Bla bla...    0          1

Answer 2

Using conditional aggregation to create groups of records I came up with this solution.

First, create and populate sample table (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    RefNo char(1),
    Whatever varchar(10),
    Seq int,
    [Value] int
)


INSERT INTO @T (RefNo, Whatever, Seq, [Value]) VALUES
('A', 'Bla bla…', 1, 1), 
('A', 'Bla bla…', 2, 1),
('A', 'Bla bla…', 3, 0),
('A', 'Bla bla…', 4, 1),
('B', 'Bla bla…', 5, 0),
('B', 'Bla bla…', 6, 1),
('B', 'Bla bla…', 7, 0),
('B', 'Bla bla…', 8, 1),
('B', 'Bla bla…', 9, 0);

Then, a common table expression to create the groups:

WITH CTE AS
(
    SELECT  RefNo, 
            Whatever, 
            Seq, 
            [Value],
            SUM(IIF([Value] = 0, 1, 0)) OVER(ORDER BY Seq) As Grp
    FROM @T
)

The query:

SELECT  RefNo, 
        Whatever, 
        [Value], 
        (
            SELECT COUNT(*) 
            FROM CTE AS T1 
            WHERE T1.Grp = T0.Grp -1
            AND T1.[Value] <> 0
            AND T1.RefNo = T0.RefNo
        ) As [Count]
FROM CTE As T0
WHERE [Value] = 0

Results:

RefNo   Whatever    Value   Count
A       Bla bla…    0       2
B       Bla bla…    0       0
B       Bla bla…    0       1
B       Bla bla…    0       1