How can I count the amount of items per user?

What I need to do is to Count the Times a User has received the treatment ‘Oxygen’. I’m trying to do like below but I’m getting all of the entries from Treatment_Victim per user. Any help is well appreciated. Should I be using something else?

ID_Victim	Name
1	John
2	Alex

ID_Treatment	Name
1	Oxygen
2	Transfussion

ID_Treatment_Victim	ID_Victim	ID_Treatment
1	1	1
2	1	1
3	1	2
4	2	1

SELECT v.Name, count(tv.ID_Treatment_Victim) AS Times
FROM Victim v, Treatment t, Treatment_Victim tv
WHERE v.ID_Victim = tv.ID_Victim
AND tv.ID_Treatment = (SELECT ID_Treatment
    FROM Treatment
    WHERE Treatment.Nombre = 'Oxigen')
GROUP BY v.ID_Victim
ORDER BY Times DESC LIMIT 5;

Answer

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax:

SELECT v.Name, COUNT(*) AS Times
FROM Treatment_Victim tv JOIN
     Victim v
     ON v.ID_Victim = tv.ID_Victim JOIN
     Treatment t
     ON v.ID_Treatment = tv.ID_Treatment
WHERE t.Nombre = 'Oxigen'
GROUP BY v.ID_Victim, v.Name
ORDER BY Times DESC
LIMIT 5;

Advertisement

Answer