CODEWITHSUNDEEP
mysql
Pr0no
Pr0no

Reputation: 4099

How do I count the number of pageviews per day of the month in MySQL?

Consider the following pageviews (left) and daterange (right) tables:

_date       pageviews       _date
---------------------       ----------
2011-01-01  21331           2011-01-01
2011-01-02  32143           2011-01-02
2011-01-04  23434           2011-01-03
.                           .
.                           .
2011-08-30  32322           2011-08-30

The table contains dates with corresponding website pageviews. Notice there are days without any pageviews (such as 2011-01-03).

What I need is a query to count the total number of pageviews per day of the month. So it should add the number of pageviews of januari 1st + februari 1st + ... + november 1st as the first value, then januari 2 + februari 2 + ... februari 3 as the second value. The output of the query wuld thus be 31 days (numbered 1 to 31) with corresponding total number of pageviews.

I have no clue on how to begin. I have created a daterange table holding all dates between 2011-01-01 and 2011-08-30 to be able to CAOLESCE and return 0 for days missing in the pageviews table. But I'm quite lost from there onwards.

Do you know how to solve this? Any help is greatly appreciated :-)

Upvotes: 2

Views: 516

Answers (3)

My Other Me
My Other Me

Reputation: 5117

Something like this?

SELECT DAY(daterange._date),SUM(pageviews)
FROM daterange 
LEFT JOIN pageviews ON daterange._date = pageviews._date
GROUP BY DAY(daterange._date)

Upvotes: 1

sundar
sundar

Reputation: 1760

SELECT DAY(_date),SUM(pageviews) FROM pageviews GROUP BY DAY(_date)

Hope the above query will solve the problem.

Upvotes: 3

Omesh
Omesh

Reputation: 29081

I think this is what you are looking for:

SELECT a._date, SUM(IFNULL(b.pageviews, 0)) AS total_pageviews
FROM daterange a
     LEFT JOIN pageviews b
         ON DAYOFMONTH(a._date) = DAYOFMONTH(b._date)
GROUP BY DAYOFMONTH(a._date) 
ORDER BY a._date;

Upvotes: 2

Related Questions