Geek asks and answers

Mysql multi count in one query

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

The table looks like this:

   user_id  |   date     | status
------------------------------
      1     | 2011-01-02 |   1
      2     | 2011-01-03 |   1
      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 is the group number user_id by status, and the second is the number of user_id by date.

How can i do this?

+3
source share
2 answers

You cannot have different GROUP BY offers in one request - each account must be in an independent request.

But you can return the result in a single query / result set using subqueries (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
+4
source

You are not .

. .

, :

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

:

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

. , .

0

More articles:


All Articles