jeudi 19 avril 2018

MySql Sum of Count If

I have a table with every login by all users. I want to run a query that will pull the number of times each user logs in but limit it to 4 if the user logged in more than 4 times. And then do a sum to get the total number of logins. Further to this I want to pull back the time frame for the total number of logins. So I specify the total number of logins as 100 then the query must pull back the earliest date, going back from today and counting the number of logins (limited at 4 if above 4) per user.

Hopefully that makes sense.

My query so far to get the list of totals limited to 4 per user:

SELECT (IF( count(l.user_id) > 4, 4, count(l.user_id))) as cappedvisited 
FROM `logins` l 
where l.store_id = 908 and l.login_dt > '2018-04-18 00:00:00' and l.login_dt < '2018-04-18 23:59:59' group by l.user_id

I'm specifying the date range at the moment but don't want to do that in the final query.

If any of this makes sense any help would be much appreciated. Thanks.

Aucun commentaire:

Enregistrer un commentaire