CODEWITHSUNDEEP
mysqlcountsum
Happy Coder
Happy Coder

Reputation: 4682

sum of counts on daily basis

I need to calculate the sum of counts of unique userids for calculating the monthly unique users and this needs to be done on a daily basis. That is for today it wil have to show the sum from 30 days before from today and for yesterday, it needs to calculate the sum beginign from 30 days before yesterday.

like for today it should start counting from 27-04-2013 and for yesterday it is from 26-04-2013. The table strucutre is like following :

date        uid   visits
27-04-2013   11     45
27-04-2013   11     45
26-04-2013   11     45
25-04-2013   11     45
24-04-2013   11     45

this is something like the running total ,but for 30 days only.

I have used DATE_ADD(date,INTERVAL -30 day) like this 

SELECT DISTINCT date,COUNT(uid) FROM tablename 
WHERE DATE_ADD(date,INTERVAL -30 day) AND date GROUP BY date

but for this I am getting diferent results compared to this query.

SELECT COUNT( `uID` )
FROM tablename
WHERE date
BETWEEN '2013-04-27'
AND '2013-05-27'

This is for COUNT only. I need to calculate the SUM of the counts.

Upvotes: 3

Views: 3350

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I cannot tell from the question whether you are trying to get this for a single day or for all days in the past. If the latter, here is one way you can do it:

SELECT DATE_ADD(date,INTERVAL n.n day) as thedate,
       COUNT(distinct uid) as numUsers, sum(visits) as totVisits
FROM tablename t cross join
     (select 0 as n union all select 1 union all select 2 union all
      select 3 union all select 4 union all select 5 union all
      . . .
      select 27 union all select 28 union all select 29
     ) n 
group by DATE_ADD(date,INTERVAL n.n day) ;

Your first query:

SELECT DISTINCT date,COUNT(uid)
FROM tablename 
WHERE DATE_ADD(date,INTERVAL -30 day) AND date
GROUP BY date

Has two shortcomings. First, the where clause is not a boolean. I think you mean something like:

where date between DATE_ADD(now(),INTERVAL -30 day) AND now()

Also, you don't need a distinct (and should not have one!) when using a group by.

EDIT:

So, run this in your Fiddle to see that it returns the numbers for each day in the data (with 6 days of history rather than 30):

SELECT DATE_ADD(mydate, INTERVAL n.n day) as thedate,
       COUNT(distinct uid) as numUsers, sum(visits) as totVisits
FROM tablename t cross join
     (select 0 as n union all select 1 union all select 2 union all
      select 3 union all select 4 union all select 5 
     ) n 
where DATE_ADD(mydate, INTERVAL n.n day) < now()
group by DATE_ADD(mydate, INTERVAL n.n day) ;

You'll notice that it skips dates that have no users.

Upvotes: 0

Fahim Parkar
Fahim Parkar

Reputation: 31637

Please don't use MySQL reserve keywords in column name. Hence I have changed date to myDate in my query.

I believe for the first step you are looking as below.

select myDate, sum(visits) as 'Total Visits' from tablename GROUP BY myDate;

Demo

For last month data, use below query.

select myDate, sum(visits) as 'Total Visits' from tablename
GROUP BY myDate
HAVING myDate BETWEEN '2013-04-27' AND '2013-05-27';

Demo

I hope this is what you are looking.

select myDate, uid, COUNT(visits) as 'Total Visits' from tablename
GROUP BY myDate
HAVING myDate BETWEEN '2013-04-27' AND '2013-05-27';

Demo

I hope this is what you want

select * from tablename 
GROUP BY uid
HAVING 
COUNT(DISTINCT myDate)>=30 
AND
myDate BETWEEN '2013-04-27' AND '2013-05-27';

For Demo purpose, I have used 3 days data in below Demo link.

Demo

Note: Changes would be on below lines

COUNT(DISTINCT myDate)>=3
                        ^^ --> This would be number of days in month (as I am using 3 days only, I have 3)

& 

myDate BETWEEN '2013-04-27' AND '2013-04-29';
                ^^^^^^^^^^^     ^^^^^^^^^^^^

I think for myDate BETWEEN '2013-04-27' AND '2013-04-29'; you should use date BETWEEN DATE_ADD(date, INTERVAL -30 day) AND CURDATE() as per Mifeet answer.

Upvotes: 4

Related Questions