Mysql - Count certain values between other values in column

eengebruiker
October 7, 2024
85 views
0 votes
2 Answers

I have the following very simplified situation in a table:

datetime id   event
-------- --   -----
dt01     67   1
dt02     67   2
dt03     106  1
dt04     106  4
dt05     106  2
dt06     654  1
dt07     654  4 
dt08     654  3
dt09     654  3
dt10     654  2
dt11     213  1
dt12     213  3
dt13     213  2
dt14     654  1
dt15     654  3
dt16     654  2

I want to count the number of 3’s between the 1 and the 2.
The table is orderend on datetime and grouped by id (I can make it appear that way).

The answer is 0 for id 67, 0 for id 106, 2 for id 654, 1 for id 213 and (again!) 1 for id 654.

The 0’s aren’t important but ok if they show up in the result. I am looking for a result that has id 654 listed twice. I can not use the sum of the answers (3 in this case). It must be 2 and 1.

I have read several tickets on stackoverflow but I do not understand them (to be honest) or they are not applicable.

I might have overlooked possibilities with window functions but I did not see a solution.
I can imagine a solution using a stored procedure but I am not allowed to add a sp.

Tags: mysql sql

Answers

For example, this can be

SELECT id, COUNT(*)
FROM table
JOIN ( SELECT id, 
              MIN(CASE WHEN event = 1 THEN datetime END) mindatetime, 
              MAX(CASE WHEN event = 2 THEN datetime END) maxdatetime
       FROM table
       WHERE event IN (1, 2)
       GROUP BY 1
       ) subquery USING (id)
WHERE table.datetime BETWEEN subquery.mindatetime AND subquery.maxdatetime
  AND table.event = 3
GROUP BY 1;

https://dbfiddle.uk/hSxExR3P

Since there are multiple groups of rows for the same id you should flag the first row of each group and then populate the flag to the rest of the group.

--      S a m p l e    D a t a :
CREATE TABLE tbl (datetime CHAR(4), id INT, event INT);
INSERT INTO tbl VALUES
('dt01',      67,  1),
('dt02',      67,  2),
('dt03',     106,  1),
('dt04',     106,  4),
('dt05',     106,  2),
('dt06',     654,  1),
('dt07',     654,  4),
('dt08',     654,  3),
('dt09',     654,  3),
('dt10',     654,  2),
('dt11',     213,  1),
('dt12',     213,  3),
('dt13',     213,  2),
('dt14',     654,  1),
('dt15',     654,  3),
('dt16',     654,  2);

… the grid cte flags the first row of each group of id rows …

WITH 
    grid AS
      ( Select     datetime, id, event,
                   Case When id != Coalesce(LAG(id) Over(Order By datetime ), 'dt00')
                        Then datetime
                   End as grp
        From       tbl
        Where      event < 4
      )

… the inner query populates the group flag to the reamining rows of the same group and outer query aggregates the data conditionaly grouping them by grp, id …

--      M a i n    S Q L : 
SELECT    id, Count(Case When event = 3 Then 1 End) as cnt
FROM    ( Select     datetime, id, event, 
                     Max(grp) 
                         Over( Order By datetime 
                               Rows Between Unbounded Preceding And Current Row ) as grp
          From       grid
          Order By   datetime
        ) x
GROUP BY    grp, id
HAVING      Min(event) = 1  And 
            Max(Case When event != 3 Then event End) = 2
ORDER BY    grp, id

… having clause should exclude the groups that doesn’t containe events 1 and/or 2 …

/*
 id  cnt
---  ---
 67    0
106    0
654    2
213    1
654    1    */

If the event 3 should be between 1 and 2 (ordered by datetime) then the HAVING clause should be changed into:

... ...
HAVING    ( Min(Case When event = 1 Then datetime End) < Min(Case When event = 3 Then datetime End) And 
            Max(Case When event = 2 Then datetime End) > Min(Case When event = 3 Then datetime End) 
          ) OR Count(Case When event = 3 Then 1 End) = 0
... ...

… the result is the same as above …

See the fiddle here.

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Count certain values between other values in column

Answers