Sasha Sasha - 1 year ago 74
MySQL Question

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.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download