Conditional count in mysql

Question

I have a table user_login with two rows, userId and tstamp (user id and timestamp of login).

Counting the number of logins is simple:

SELECT userId, COUNT(*) as logins FROM user_login GROUP BY userId;

Counting the number of logins before a certain date (e.g. Jan 1 2018) is also simple:

SELECT userId
     , COUNT(*) as loginsBeforeJan1 
  FROM user_login 
 WHERE tstamp < '2018-01-01' 
 GROUP 
    BY userId;

How can I have both results in one table? I tried

SELECT userId
     , COUNT(*) as logins
     , COUNT(IF(tstamp < '2018-01-01',1,0)) loginsBeforeJan1 
  FROM user_login 
 GROUP 
    BY userId;

but both the logins and loginsBeforeJan1 are identical.

How can I modify the query to produce the result I want?

ScaisEdge · Accepted Answer · 2018-07-04 16:00:41Z

5

You could use a sum (and group by)

SELECT userId, COUNT(*) as logins, sum(IF(tstamp < '2018-01-01',1,0)) as loginsBeforeJan1 
FROM user_login
group by userId;

answered Jul 4, 2018 at 15:58

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

2 Comments

Oops, I forgot to add the GROUP BY! I'll edit my question

Ok .. for group by but you should use sum and not count

Roshana Pitigala · Accepted Answer · 2018-07-04 17:57:58Z

0

Try using the WHERE clause instead of the IF statement in your query

Roshana Pitigala

8,8569 gold badges53 silver badges87 bronze badges

answered Jul 4, 2018 at 15:58

Chike Ibezim

12 bronze badges