How can I count rows up to a specific value in SQL

June 27, 2023

I have a database table that looks like this (although it is much larger!)

playerid	dismissaltype	Offset
2	0	0
2	1	1
2	0	2
2	0	3
2	0	4
2	0	6
2	2	7
3	1	0
3	2	1
3	0	2
3	1	3
3	0	4
3	0	5
5	0	1
5	1	2
5	0	2
5	0	3
6	1	0

I want to count the rows for a given dismissaltype until that dismissaltype is 0 in the order given by the offset column and return the results for each playerid so for the above
the result should be.

playerid	count
2	1
3	3
5	1
6	0

(The offset column is an integer but in reality contains non-contiguous values)

This is for MySql.

>Solution :

On MySQL 8+, we can use a combination of LAG() and SUM() here:

WITH cte AS (
    SELECT *, LAG(dismissaltype, 1, 1) OVER (PARTITION BY playerid
                                             ORDER BY Offset) lag_dismissaltype
    FROM yourTable
),
cte2 AS (
    SELECT *, SUM(lag_dismissaltype = 0) OVER (PARTITION BY playerid
                                               ORDER BY Offset) AS zero_sum
    FROM cte
)

SELECT playerid,
       CASE WHEN SUM(dismissaltype = 0) > 0
            THEN SUM(zero_sum = 0)
            ELSE 0 END AS count
FROM cte2
GROUP BY playerid
ORDER BY playerid;

The basic strategy here is to take a running count of the dismissal type values when they are non zero, from the start of the offset. We then aggregate by player and count how many records thers were before hitting the first zero dismissal.