0

I want to count all clicks based on daily level from a date range.

Let's say I want to count all clicks between "2013-05-01" - "2013-05-30" and want the following result to be something like this:

date            clicks
2013-05-01      50
2013-05-02      60
2013-05-03      65
2013-05-04      0
etc...

At the moment I've the following SQL call

SELECT count(date) as clicks, date FROM views WHERE uid = $id group by date ORDER BY date DESC

This is working fine, the only problem is, that I don't get all the days in the date range, but only the days where there is clicks. I should be able to get all days, also the days where there is 0 clicks.

Hope someone can help

Simon Thomsen
  • 1,351
  • 7
  • 27
  • 37
  • you mean you get only dates that are in date column? Or you dont get 2012-05-04 with 0 click which is really strange? – Bojan Kovacevic May 14 '13 at 09:47
  • 3
    How can your database return you data which it does not even have. If there are some days not being displayed that means there is no data for them, you can easily add that to your PHP code to display those dates with 0 count – Hanky Panky May 14 '13 at 09:48
  • 1
    possible duplicate of [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) –  May 14 '13 at 09:58

5 Answers5

3

Since you are only getting results from days where there are clicks, you won't get any results for days with no clicks. You need to either

A) Generate a list of dates and return number of clicks for each day. Similar to SQL Server: How to select all days in a date range even if no data exists for some days

B) Instead of using the resulting rows for the output, enumerate each day in PHP and insert a 0 entry into the output if the day is not found in the SQL result set. You can use date_add to add one day at a time to the period start DateTime and format it as a string with date to check it against the result's date.

Community
  • 1
  • 1
Elle
  • 3,695
  • 1
  • 17
  • 31
0

Try using BETWEEN clause...

SELECT * FROM table_name WHERE column_name BETWEEN start_date AND end_date
Mr. Alien
  • 153,751
  • 34
  • 298
  • 278
  • this is fine, though i think he want to results for all dates not just the ones he has in db. And i didnt gave you -1 btw. – Bojan Kovacevic May 14 '13 at 09:50
  • 1
    Doesn't work. It should return all the dates in the date range and count clicks on that day. That example is only returning days where there is any clicks :-) – Simon Thomsen May 14 '13 at 09:52
  • @SimonThomsen Don't copy paste the comments, first check the query, do you see any condition here which selects rows only with clicks? Am simply giving a range to the results – Mr. Alien May 14 '13 at 09:53
  • 1
    No, but your example still doesn't work - so it's the same "problem" :-) – Simon Thomsen May 14 '13 at 09:55
  • @to the person who is upvoting comments - Do you see any condition which ONLY selects rows which have clicks? – Mr. Alien May 14 '13 at 09:56
  • 1
    you two dont understand each other, he expect query to return all dates in range, not only the one he has in database. – Bojan Kovacevic May 14 '13 at 09:57
  • @BojanKovacevic I provided an answer for MySQL which suffices the normal issue, his needs are awkward and he need to do that with server side – Mr. Alien May 14 '13 at 10:00
  • 1
    @Mr.Alien i agree, and i commented that too in other answer. – Bojan Kovacevic May 14 '13 at 10:01
0

Try like this, because right now you are getting the values in the date range.

SELECT count(date) as clicks, date FROM views 
WHERE uid = $id AND date BETWEEN $from_dt AND $to_dt 
group by date ORDER BY date DESC;

Also, this query will return counts only if your table have any records on the particular date.

Edwin Alex
  • 5,118
  • 4
  • 28
  • 50
  • Doesn't work. It should return all the dates in the date range and count clicks on that day. That example is only returning days where there is any clicks :-) – Simon Thomsen May 14 '13 at 09:53
  • @SimonThomsen Not only this. Whatever the query you write, it will return only if table has data. In this case, you need to generate all the dates separately and then you should get the counts for the date. – Edwin Alex May 14 '13 at 09:56
0
SELECT count(date) as clicks, date 
FROM views 
WHERE uid = $id
  AND date between 'date1' and 'date2'
group by DAY(date)
ORDER BY date DESC
M Reza Saberi
  • 7,134
  • 9
  • 47
  • 76
  • Doesn't work. It should return all the dates in the date range and count clicks on that day. That example is only returning days where there is any clicks :-) – Simon Thomsen May 14 '13 at 09:51
  • nope,it retuns dates THAT ARE IN DB! You cant expect query to be intelligent and make rest of dates for you. You have to first find all dates (using date functions in mysql) and then check count for each one of them. Or as somebody suggested in php set 0 for dates that doesnt exist in db. – Bojan Kovacevic May 14 '13 at 09:54
0

try this:

select sum(clicks) as total_clicks,date from views where uid='$id' AND (date BETWEEN $from_dt AND $to_dt) group by date order by date desc
abdul quadir
  • 24
  • 1
  • 7