CODEWITHSUNDEEP
mysql
wwl
wwl

Reputation: 2065

Conditional count in mysql

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?

Upvotes: 4

Views: 2754

Answers (2)

Chike Ibezim
Chike Ibezim

Reputation: 1

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

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

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;

Upvotes: 5

Related Questions