How to use sum(count()) in mysql

Question

I wish to get the total number of users registered with my site in the date range selected like as follows:

2 users logged in on 1st June
1 user on 2nd June,
2 users on 3rd June

Then I need the count for

1st June as 2

2nd June as 3

3rd June as 5

Here is my query

SET @runtot:=0;
SELECT
    count(u.app_user_registered_date) as users_count,
    (@runtot := count(u.app_user_registered_date) + @runout) AS rt,           
    date(u.app_user_registered_date) as registered_date
FROM entr_user as u
INNER JOIN entr_app_granted_permission_details as g
ON g.device_id=u.device_id
WHERE date(u.app_user_registered_date) between '2016-05-31' and '2016-06-03'    
GROUP BY date(u.app_user_registered_date) ORDER BY u.app_user_registered_date;

But the rt value is NULL here

I think it's because you SET runtot:=0; but then use + runout - runout will be null, and adding null to any other value yields null... — Neville Kuyt, Jun 08 '16 at 05:47

score 1 · Answer 1 · edited May 24 '20 at 16:38

Perhaps you have a need for the rt. One can't tell. However, there seems to be a typo in your Variable: runtot. Presumably, you wanted to write: runout. That was why rt returns null. Here:

SET @runout:=0;
SELECT
COUNT(u.app_user_registered_date) AS users_count,
(@runout := count(u.app_user_registered_date) + @runout) AS rt,
DATE(u.app_user_registered_date) AS registered_date
FROM entr_user as u
INNER JOIN entr_app_granted_permission_details as g
ON g.device_id=u.device_id
WHERE date(u.app_user_registered_date) between '2016-05-31' and '2016-06-03'
GROUP BY date(u.app_user_registered_date) ORDER BY u.app_user_registered_date;";

Mani · Answer 2 · 2016-06-08T06:39:48.090

Use like this.. Total users on that day

SELECT u.app_user_registered_date,COUNT(u.id) as users FROM `entr_user` as u 

INNER JOIN entr_app_granted_permission_details as g ON g.device_id=u.device_id

WHERE date(u.app_user_registered_date) between '2016-06-07' and '2016-06-10' GROUP BY u.app_user_registered_date

Total user on that day and before that days upto from_date what we given

SELECT u.app_user_registered_date,COUNT(u.id) as users,

(select COUNT(h.id) from entr_user as h 
    INNER JOIN entr_app_granted_permission_details as i ON i.device_id=h.device_id
    where (DATE(h.app_user_registered_date) between '2016-06-07' and '2016-06-10') and
    DATE(h.app_user_registered_date)<=DATE(u.app_user_registered_date) 
)as total_users

FROM `entr_user` as u 
INNER JOIN entr_app_granted_permission_details as g ON g.device_id=u.device_id
WHERE date(u.app_user_registered_date) between '2016-06-07' and '2016-06-10' GROUP BY u.app_user_registered_date

How to use sum(count()) in mysql

2 Answers2