mysql combining different count() on same table

Question

My database holds a 'lastOnline' timestamp variable that get updates when a user logs in. I want to use this to retrive 3 values

[The total amount of users][users online today][users online in the last hour].

I can do each statement seperately

SELECT count(u.id) AS usersTotal FROM user u

SELECT count(u.id) AS usersDay FROM user u
WHERE u.lastOnline >= now() - INTERVAL 1 DAY

SELECT count(u.id) AS usersHour FROM user u
WHERE u.lastOnline >= now() - INTERVAL 1 HOUR

but I am having trouble joining/union the 3 of them into one query

Thank you

Lukasz Szozda · Accepted Answer · 2017-12-02 22:42:22Z

1

You could use:

SELECT count(u.id) AS usersTotal 
      ,SUM(u.lastOnline >= now() - INTERVAL 1 DAY)  AS usersDay
      ,SUM(u.lastOnline >= now() - INTERVAL 1 HOUR) AS usersHour
FROM user u;

How it works:

u.lastOnline >= now() - INTERVAL 1 DAY return true(1) or false(0).

answered Dec 2, 2017 at 22:42

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Rik1988 Over a year ago

Perfect... Thanks alot!

Collectives™ on Stack Overflow

mysql combining different count() on same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related