Question 195234 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Using a cte to find series of non-zero dismissaltypes and then grouping on the series’ IDs:

    with cte as (
    select p.*, case when p.dismissaltype = 0 then 0 else (select sum(p1.playerid = p.playerid and p1.offset < p.offset and p1.dismissaltype = 0) 
       from players p1) + 1 end k 
    from players p
)
select distinct p.playerid, coalesce(t1.s, 0) from players p 
left join (select t.playerid, sum(t.c) s from (select c.playerid, c.k, count(*) c, max(c.offset) m 
   from cte c where c.dismissaltype != 0 group by c.playerid, c.k) t 
   where exists (select 1 from players p1 where p1.playerid = t.playerid and p1.dismissaltype = 0 and p1.offset = (select min(p2.offset) 
        from players p2 where p2.playerid = p1.playerid and p2.dismissaltype = 0 and p2.offset > t.k)) 
   group by t.playerid) t1 
on t1.playerid = p.playerid

    See fiddle

    Login or Signup to reply.
  3. 0

    To achieve the desired result in MySQL, you can use a combination of subqueries and variables. Try the following query which counts the rows for each playerid until the dismissaltype is 0, based on the given Offset column:

    SELECT playerid, MAX(running_count) AS count
FROM (
    SELECT playerid, dismissaltype, Offset,
        CASE WHEN dismissaltype = 0 THEN @count := 0 ELSE @count := @count + 1 END AS running_count
    FROM YourTable
    CROSS JOIN (SELECT @count := 0) AS c
    ORDER BY playerid, Offset
) AS subquery
GROUP BY playerid;

    This query uses a subquery to assign a running count to each row based on the dismissaltype and Offset columns. The running count is reset to 0 whenever dismissaltype is 0. Finally, the outer query groups the results by playerid and selects the maximum running count for each player.

    The expected result will be:

    playerid count
    2 1
    3 3
    5 1
    6 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search