Get the users that used their card for N amount of times between 2 dates

user_17329874 0
Viewed 23

I want to get the users(Gebruiker) that used their card(pas) for N amount of times between 2 dates.

Now I tried to use HAVING COUNT or some variation of that, to get the amount of times a row contains a foreign key and use that to filter out everyone that used it atleast 5 times between 2 dates.

This is what I tried but without the date:

SELECT *, COUNT(Pas_pasnummer)
FROM gebruiker
INNER JOIN pas_has_poort ON gebruiker.gebruikernummer=pas_has_poort.Pas_pasnummer
HAVING COUNT(Pas_pasnummer) = '1'

This is my database: Database model

mysql
1 Answers

gebruiker.gebruikernummer is not a foreign key to pas_has_poort.Pas_pasnummer. You need to go through the linking table pas.

You need to group the query by gebruikernummer.

Then you can put a condition on the date in pas_has_poort.

SELECT g.*, COUNT(*) AS use_count
FROM gebruiker AS g
JOIN pas AS p ON g.gebruikernummer = p.gebruikergebruikernummer
JOIN pas_has_poort AS pp ON p.pasnummer = pp.Pas_pasnummer
WHERE pp.inchecktijd BETWEEN @start_date AND @end_date
GROUP BY g.gebruikernummer
HAVING use_count >= 5
user_14918950
Related