MySQL select since sum >= 3?

Question

Table:

+++++++++++++++++++++++++
+ id | event | group_id +
+  1 |  '+1' |        1 +
+  2 |  'pt' |        1 +
+  3 |  'pt' |        1 +
+  4 |  '+1' |        1 +
+  5 |  'pt' |        1 +
+  6 |  '+1' |        1 +
+  7 |  'pt' |        1 +
+  8 |  '+1' |        1 +
+++++++++++++++++++++++++

I need to select SUM(CASE WHEN event = '+1' THEN 1 ELSE 0 END) since SUM(CASE WHEN event = 'pt' THEN 1 ELSE 0 END) = 3.

I tried this:

SELECT group_id, SUM(CASE WHEN event = '+1' THEN 1 ELSE 0 END) AS event_sum
FROM Table
WHERE SUM(CASE WHEN event = 'pt' THEN 1 ELSE 0 END) >= 3
ORDER BY id
GROUP BY group_id

But this is not the way how group function works, so how could I achieve this output ? Do I need to use user variable to select id when condition SUM(CASE WHEN event = 'pt' THEN 1 ELSE 0 END) = 3 is true ? I believe that it is possible to do it without user variable, is it possible ?

Desired output:

++++++++++++++++++++++++
+ group_id | event_sum +
+        1 |         2 +
++++++++++++++++++++++++

first sum up where event =pt mark as temp table and then select and count from temp where event >=3 — Bhavik Patel, Oct 10 '16 at 19:27

score 1 · Accepted Answer · edited May 23 '17 at 12:19

You want the number of all +1 events since the moment you get at least 3 'pt' events.

For 'since', I guess you want to order the events by ID.

For pt, you need a running total. To achieve this in MySQL there are several answers on Stack Overflow (this is one).

SET @pts:=0;
SELECT
   *,
   (@pts := @pts + (CASE WHEN event='pt' THEN 1 ELSE 0)) AS pts
FROM Table 
ORDER BY id;

But you want the count to be reset at every change of groupid, so:

SET @pts:=0,@gid:=-1;
SELECT *,
    (@pts := IF (@gid != groupid, 0, @pts + IF(event='pt', 1, 0))) AS pts, 
    @gid:=groupid
FROM eventi ORDER BY groupid, id;

So at every loop you save the current groupid value, but before doing so, you check if it's the same. If it is not, then you zero pts count.

+------+-------+---------+------+---------------+
| id   | event | groupid | pts  | @gid:=groupid |
+------+-------+---------+------+---------------+
|    1 | pt    |       1 |    0 |             1 |
|    2 | pt    |       1 |    1 |             1 |
|    3 | +1    |       1 |    1 |             1 |
|    4 | pt    |       1 |    2 |             1 |
|    5 | +1    |       1 |    2 |             1 |
|    6 | +1    |       1 |    2 |             1 |
|    7 | pt    |       2 |    0 |             2 |
|    8 | pt    |       2 |    1 |             2 |
|    9 | +1    |       2 |    1 |             2 |
|   10 | pt    |       2 |    2 |             2 |
|   11 | +1    |       2 |    2 |             2 |
|   12 | +1    |       2 |    2 |             2 |
+------+-------+---------+------+---------------+

From here you see that the number of actual pts' is off by one (the check is done in reverse order than the increment).

Now you can do the grouping:

SET @pts:=0,@gid:=-1; 
SELECT groupid, SUM(IF(pts >= 3 AND event='+1', 1,0)) AS event_sum FROM (
    SELECT *,
       (@pts := IF(@gid!=groupid, 0, @pts+IF(event='pt',1,0)))+1 AS pts,
       @gid:=groupid
    FROM eventi ORDER BY groupid, id 
) AS a GROUP BY groupid;


+---------+-----------+
| groupid | event_sum |
+---------+-----------+
|       1 |         2 |
|       2 |         2 |
+---------+-----------+

You can merge the SET in the same query, too:

SELECT ...  AS a, (SELECT @pts:=0,@gid:=-1) AS i GROUP BY groupid;

This is a test SQLfiddle.

MySQL select since sum >= 3?

1 Answers1