I have a system that generates a report for the number of web hits for a collection of pages.
For each row, as well as details of the page, the report has 3 columns that show TOTAL hits, hits this MONTH and hits this WEEK.
My current deployed SQL syntax uses 2 subqueries to get the monthly and weekly figures, but with the number of rows im dealing with it takes too long. I'm trying to use LEFT JOINS to get all the data in one query but having a couple of issues.
I've provided the table data below. I would expect the 3 columns for page_id #0 to be: TOTAL=3, MONTH=2, WEEK=1
Instead all 3 are equal to 6 so it's obviously adding the results somewhere.
Am i doing this the right/best way? Top marks for a great solution - it's been bugging me for a while so i'll be glad to fix it
SELECT * FROM pages;
0 Product A
1 Product B
2 Product C
3 Product D
SELECT * FROM page_hits;
hit_id hit_date page_id ip_address
0 2009-01-01 0 192.168.0.1
1 2009-04-01 0 192.168.0.1
2 2009-04-22 0 192.168.0.1
3 2009-01-01 1 192.168.0.1
/* MY REPORT QUERY IS AS FOLLOWS... */
COUNT(b.hit_id) AS 'Total Hits'
COUNT(c.hit_id) AS 'This Weeks Hits'
COUNT(d.hit_id) AS 'This Months Hits'
FROM pages a
LEFT JOIN page_hits b ON (b.page_id = a.page_id)
LEFT JOIN page_hits c ON (c.page_id = a.page_id AND c.hit_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
LEFT JOIN page_hits d ON (d.page_id = a.page_id AND d.hit_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
WHERE a.page_is_active = 'true'
GROUP BY a.page_id
ORDER BY a.page_id