join results of two mysql queries on the same table

Question

I have the intuition that I'm missing something simple, so please excuse me if it's a stupid question but I haven't been able to find an answer here.

I'm treating a database with usage behaviors. We have one row per user, with date and time spent (plus other non-relevant info).

I'd like to output a histogram of the number of visits per day, and number of visits that lasted more than a certain time ; ideally I'd like to have that in one query.

For now I have these two queries:

SELECT DATE(date), COUNT(date) AS Number_of_users FROM users GROUP BY DATE(date)
SELECT DATE(date), COUNT(date) AS Number_of_stayers FROM users WHERE timespent>5 GROUP BY DATE(date)

How can I combine them to obtain a result in the form of:

date        users       stayers
2014-01-01  21          5
2014-01-02  13          0

etc.

Thanks in advance for any help!

Answer 1

You can try using IF, like this:

SELECT DATE(date), 
       COUNT(date) AS Number_of_users,
       SUM(IF(timespent>5,1,0)) AS Number_of_stayers 
FROM users 
GROUP BY DATE(date)

Answer 2

This should work, or at least show the basic idea of using JOINs:

SELECT DATE(a.date), 
    COUNT(a.date) AS Number_of_users, 
    COUNT(b.date) AS Number_of_stayers 
FROM users a 
LEFT JOIN users b ON (a.date = b.date AND b.timespent>5)