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...

问题:

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?


回答1:

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;

回答2:

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

  • 发表于 2018-07-09 14:05
  • 阅读 ( 257 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除