Mysql multi count in one query

How can I count records for two columns in one table using different query criteria?

Table looks like:

   user_id  |   date     | status
------------------------------
      1     | 2011-01-02 |   1
      2     | 2011-01-03 |   1开发者_StackOverflow
      3     | 2011-01-02 |   0
      4     | 2011-01-03 |   1
      1     | 2011-01-02 |   1

I want to count two values in one query. The first one is number of user_id group by status and the second is count of user_id group by date.

How can I do that?

You can't have different GROUP BY clauses in the same query -- each count will have to be in an independent query.

But you can return the output in a single query/resultset using subselects (subquery in the SELECT clause):

   SELECT COUNT(a.user_id) AS numUsersPerStatus,
          (SELECT COUNT(b.user_id)
             FROM YOUR_TABLE b
         GROUP BY b.date) AS numUsersPerDate
    FROM YOUR_TABLE a
GROUP BY a.status

You don't.

You should use two queries. There's no advantage to doing this with a single query.

If you really want to do it you can try this:

SELECT 'date' AS grptype, date AS grp, COUNT(DISTINCT user_id) AS cnt
FROM yourtable
GROUP BY date
UNION ALL
SELECT 'status' AS grptype, status AS grp, COUNT(DISTINCT user_id) AS cnt
FROM yourtable
GROUP BY status

Result:

grptype    grp         cnt
date       2011-01-02  2  
date       2011-01-03  2  
status     0           1  
status     1           3  

However I would strongly advise against doing this. You want two different and unrelated result sets so you should use two separate queries.