MySQL: select sum of column having specific values

user_4981724 0
Viewed 48

What I'm trying to do is to select a specific amount of tickets (max 2) and every person that has the sum of the number of tickets less of 3 and the valid field has to be != 'e'

I have this table:

ID id_person nr_tickets valid
1 220 1 s
2 220 1 s
3 330 2 s
4 330 1 e
5 331 1 s
6 220 2 s
7 441 1 s
8 442 2 s
9 443 1 s
10 444 1 s
11 445 2 s

Here is what I did:

SELECT m.id, m.id_person, m.nr_tickets, m.valid
FROM   table m
JOIN   table m1 ON m1.id <= m.id 
WHERE  m.nr_tickets > 0 
GROUP  BY m.id
HAVING SUM(case when m.valid != 'e' then m1.nr_tickets end) <= 10

This query gives me

ID id_person nr_tickets valid
1 220 1 s
2 220 1 s
3 330 2 s
5 331 1 s
6 220 2 s
7 441 1 s
8 442 2 s

As you can see the query it's almost right, the thing is that the person 220 in the results has the sum of the tickets is greater than 2.

What I'm trying to achieve is to bypass the ID 6, and to have instead the ID 9

mysql sql
1 Answers
select `id`,`id_person`, sum(`nr_tickets`) as `nr_tickets`, `valid` 
from `test`
group by `id_person`
having sum(`nr_tickets`) < 3 and `valid`!="e"

Output:

id  id_person   nr_tickets  valid
5      331        1           s
7      441        1           s
8      442        2           s
9      443        1           s
10     444        1           s
11     445        2           s
user_52917930
Related