CODEWITHSUNDEEP
mysqldatabase
holy
holy

Reputation: 632

complicated select query with join and group by

I have two mysql tables and i want to do a select query: My objective is to calculate for a certain subscriber his points for the last seven days starting from the current time . The first table presents for each subscriber the time he hits on a certain content from content id 1 2 or 3. He could for a certain day have no content hits so he will not have points for this day.

enter image description here

enter image description here

The tables are shown in these pictures. MY select query which is incomplete :

   SELECT SUM( points ) AS POINT, DAY FROM
 (SELECT content_hits.content_id, COUNT( * ) * points AS points, 
DATE_FORMAT( hit_time,  "%d-%m-%Y" ) AS  DAY 
FROM content_hits JOIN content 
WHERE content_hits.content_id = content.content_id AND subscriber_id =1
 AND DATE_FORMAT( hit_time,  "%Y-%m-%d" ) > ( CURDATE( ) - INTERVAL 7 DAY )
 GROUP BY content_hits.content_id, DAY) AS tm 
GROUP BY DAY 
ORDER BY DAY DESC

The result is shown in this picture: enter image description here

I expects having a result with zero count for the days: 17-7-2102 16-7-2012 15-7-2012 14-7-2012 and 12-7-2012. Any suggestion?

Upvotes: 0

Views: 296

Answers (1)

sel
sel

Reputation: 4957

Need to create calendar table for use in left join

CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

Left join the calendar table to your query.

 SELECT cal.date,COALESCE(a.point,0) as point
 FROM (
    SELECT DATE_FORMAT(CURDATE() + INTERVAL a.i * 10 - b.i DAY,"%Y-%m-%d") as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 - b.i
 ) cal LEFT JOIN 
 (SELECT SUM( points ) AS POINT, DAY FROM
  (SELECT content_hits.content_id, COUNT( * ) * points AS points, 
    DATE_FORMAT( hit_time,  "%Y-%m-%d" ) AS  DAY 
   FROM content_hits JOIN content 
   WHERE content_hits.content_id = content.content_id AND subscriber_id =1
     AND DATE_FORMAT( hit_time,  "%Y-%m-%d" ) > ( CURDATE( ) - INTERVAL 7 DAY )
   GROUP BY content_hits.content_id, DAY) AS tm 
   GROUP BY DAY 
 )a
 ON cal.date = a.day
 WHERE cal.date BETWEEN CURDATE( ) - INTERVAL 7 DAY AND CURDATE()
 ORDER BY cal.date desc;

SQL DEMO here.

Upvotes: 1

Related Questions