MySQL COUNT Filtering

Question

This is my first post on stackoverflow, although I've used it as a reference for a long time. So thanks for all the guidance you've provided me in the past. I really appreciate it. Here is my issue:

I have two tables, one called USERS and one called SIGNIN

USERS  
-----------------
userid | password

SIGNIN
--------------
suserid | date

I would like to count the number of times that a user has signed in over the period of the last week.

Here's what I've got:

SELECT userid, password FROM USERS JOIN (SELECT suserid, COUNT(*) AS logins 
FROM signin 
WHERE WEEKOFYEAR(date) = WEEKOFYEAR(CURDATE()) && signin.suserid = users.userid) 
GROUP BY userid

I just can't wrap my head around how to JOIN a subquery and make it count each individuals' logins (date column) and return an individualized number for each individual. I know that query is totally jacked up but I'm just at that point, you know, where I've become so confused that I just need some guidance.

Any help and direction would be fantastic! I've read so many pages to no avail. Thanks, in advance!

** and thank you for the edits. They'll help in the future when I post again.

ysth · Accepted Answer · 2013-01-06 23:26:03Z

2

Don't try to do a subquery, just do the join and group by user id. Also, you want YEARWEEK so you are matching both the year and week, not just the week from any year.

select userid, count(*)
    from users inner join signin on userid=suserid and yearweek(`date`) = yearweek(curdate())
    group by userid;

left join instead of inner join if you want results of 0 for users who haven't signed in. This follows what I assume is your intent of "last week" meaning the week of the current date (by default starting Sunday, but see the optional mode parameter to yearweek).

Note that the users table here is completely optional; you could just do:

select suserid, count(*) from signin
    where yearweek(`date`) = yearweek(curdate())
    group by suserid;

but I'm guessing you may be getting other columns from user or wanting to exclude signins for deleted users.

edited Jan 6, 2013 at 23:26

answered Jan 6, 2013 at 22:09

ysth

99.1k6 gold badges126 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andriy M Over a year ago

I don't see why using a subquery would be an issue, but the point about changing to YEARWEEK is very good.

ysth Over a year ago

the questioner was confused and trying to do a subquery to do what a join naturally does. subqueries can be powerful in some cases, but mysql can handle them rather differently than a seemingly equivalent join; they should be avoided unless you know you need one.

Andriy M Over a year ago

Ah, yes, that seems a fair point. Do you think you could incorporate some of this reasoning into your answer? I'm not insisting, it's just that a mere ‘Don't try to do a subquery’ without any explanation may sound ambivalent to some. ‘Don't try to do a subquery because it won't do the job properly’? We shouldn't forget that many people can come across this thread and may try applying answers here to their problems.

Wilhelm Warner Over a year ago

Exactly what I needed. So I was way overcomplicating it by trying to do a subquery. You were also exactly right about the "last week" meaning the week of the current date (which defaults to starting on Sunday). REALLY appreciate the help, stackoverflowers! Thank you!

JanT · Accepted Answer · 2013-01-06 22:02:33Z

0

sThis should be it:

select suserid, count(suserid)
    from signin
    where date > NOW() - INTERVAL 1 WEEK
    group by suserid;

answered Jan 6, 2013 at 22:02

JanT

2,0963 gold badges30 silver badges38 bronze badges

Comments

FJT · Accepted Answer · 2013-01-06 22:05:45Z

0

You don't need a subquery if I understand you correctly.

Something like:

SELECT a.userid, a.password, count(b.date) AS logins
FROM users a
INNER JOIN signin b ON a.userid = b.suserid  and WEEKOFYEAR(date) = WEEKOFYEAR(CURDATE())
GROUP BY a.userid, a.password

Sorry - haven't got time to try the query to double check syntax, but hopefully will set you in right direction

Edit: If you want to include users with 0 logins, change to LEFT OUTER JOIN

answered Jan 6, 2013 at 22:05

FJT

2,0831 gold badge16 silver badges14 bronze badges

Comments

mamdouh alramadan · Accepted Answer · 2013-01-06 22:06:57Z

0

you don't need a join here. what you need is

SELECT userid, password, (SELECT COUNT(*) 
FROM SIGNIN
WHERE WEEKOFYEAR(date) = WEEKOFYEAR(CURDATE()) AND signin.suserid = users.userid) AS logins
FROM USERS WHERE 1 GROUP BY userid;

answered Jan 6, 2013 at 22:06

mamdouh alramadan

8,5166 gold badges39 silver badges53 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT Filtering

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related