• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1912
  • Last Modified:

MySQL Record Count by Total, Week, Month

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;
page_id   page_name
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... */
 
 
SELECT
  a.page_id,
  a.page_name,
  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

Open in new window

0
ianmair329
Asked:
ianmair329
  • 6
  • 4
1 Solution
 
tcs224694Commented:
Hi can u please explain a bit clear...

>>>>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.
0
 
ianmair329Author Commented:
If i run the query i posted against the tables i posted, the data returned is as i quoted there. Have summarized in the tables below...

TABLE 1 - What i want
page_id   page_name  Total_Hits  Hits_this_month  Hits_this_week
0         ProductA   3           2                1
1         ProductB   1           0                0
2         ProductC   0           0                0
3         ProductD   0           0                0
 
 
TABLE2 = What i'm getting
page_id   page_name  Total_Hits  Hits_this_month  Hits_this_week
0         ProductA   6           6                6
1         ProductB   1           0                0
2         ProductC   0           0                0
3         ProductD   0           0                0

Open in new window

0
 
ianmair329Author Commented:
hi
still looking for a solution to this
thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
tcs224694Commented:
Hi can u please post the result for this query...
SELECT
  a.page_id,
  a.page_name,
  COUNT(b.hit_id) AS 'Total Hits'
FROM pages a
  LEFT JOIN page_hits b ON (b.page_id = a.page_id)
 
WHERE a.page_is_active = 'true'
GROUP BY b.page_id
ORDER BY a.page_id

Open in new window

0
 
ianmair329Author Commented:
See first table below for that query's results. Notice only pages that occur in the hits table are returned, so i modified your query and grouped by A.page_id to show all - this way pages without any hits will also display (second table).

The figures are correct for the TOTAL HITS, so it's just working out how to show the hits THIS MONTH and THIS WEEK in subsequent columns now!
page_id   page_name  Total_Hits
0         ProductA   3
1         ProductB   1
 
 
 
page_id   page_name  Total_Hits
0         ProductA   3
1         ProductB   1
2         ProductC   0
3         ProductD   0

Open in new window

0
 
tcs224694Commented:
I dont have time here in office.I will send the query tomorrow...
0
 
tcs224694Commented:
Hi when i try to do it separately it is working fine.But i cant merge it.

Please click the request attention button and ask help from other experts.

Thanks,TCS
0
 
ianmair329Author Commented:
ok thanks have done that.

Yeah it's a pain. I really didn't want to do it with 3 subqueries as there are so many rows, but if noone can help looks like i'll just have to treat it as a lengthy process!
0
 
SharathData EngineerCommented:
No need to LEFT JOIN page_hits table in line 26 and 27 of your original query.
You can try with SUM and CASE combination. give a try.
SELECT a.page_id,
       a.page_name,
       COUNT(b.hit_id) AS 'Total Hits',
       SUM(CASE WHEN b.hit_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) THEN 1 ELSE 0 END) AS Hits_this_month,
       SUM(CASE WHEN b.hit_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) THEN 1 ELSE 0 END) AS Hits_this_week
  FROM pages a
  LEFT JOIN page_hits b ON (b.page_id = a.page_id)
 WHERE a.page_is_active = 'true'
 GROUP BY a.page_id,a.page_name
 ORDER BY a.page_id

Open in new window

0
 
ianmair329Author Commented:
Thanks Sharath_123, i'm at work at the moment (GMT) will give it a try when i get back and respond. Looks promising though!
0
 
ianmair329Author Commented:
Nice one!
i just had to remove a bracket from just before MONTH and WEEK in your case statements, but we'll hardly complain about that!
many thanks that's exactly what i was after
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now