CODEWITHSUNDEEP
mysqldatetimeaggregate-functions
Sasha
Sasha

Reputation: 8705

MySQL - count users for each day and count them all

Is it possible to count new users for each day and count all users in the table in the same query? This query is working (it is counting user per day), I just need to add part where it is counting all users.

SELECT COUNT(iduser) as newUsers, add_date
FROM cs_user
GROUP BY DAY(add_date)

Example I need:

 newUsers   add_date    
1   2016-05-05 17:33:49
5   2016-03-10 16:44:33
2   2016-04-15 19:50:42
5   2016-05-16 18:46:52
6   2016-04-18 15:01:34
1   2016-03-21 23:50:40
2   2016-03-22 18:22:48
3   2016-03-23 22:02:45
2   2016-03-25 16:38:56
15  2016-04-27 18:05:43
3   2016-04-29 04:54:05
23  2016-03-30 17:27:26

I need a new row where it should writhe the number of all users.

Upvotes: 1

Views: 2619

Answers (2)

O. Jones
O. Jones

Reputation: 108651

You can use the WITH ROLLUP option to GROUP BY. This will add an extra row to the result set that contains the overall COUNT().

By the way, the DATE() function produces the date from a DATETIME item, such as your add_date column. The DAY() function produces the day of the month. You should decide which one you need.

I think this query will do what you want.

SELECT COUNT(iduser) as newUsers, DATE(add_date) day
  FROM cs_user
 GROUP BY DATE(add_date) WITH ROLLUP

Upvotes: 4

apomene
apomene

Reputation: 14389

you can use union:

SELECT COUNT(iduser) as newUsers, add_date
FROM cs_user
GROUP BY DAY(add_date)

UNION ALL
SELECT   COUNT(iduser) as newUsers, NOW()
FROM cs_user

Upvotes: 2

Related Questions