Mysql – How to get this thesql query to include null values


Im writing a pruning script, to delete content from my site that was uploaded over a week ago, and been accessed 0 or 1 times, also in the last week.

there are 2 tables:

  • daily_hits – which stores the item id, date, and number of hits that item got on that date.
  • videos – stores actual content

I came up with this.

$last_week_date = date('Y-m-d',$now-(60*60*24*7));
$last_week_timestamp = $now-(60*60*24*7); 


            COALESCE(sum(hit_hits),0) as total_hits 
        FROM videos
        LEFT JOIN daily_hits
            ON vid_id = hit_itemid 
        WHERE (hit_date >= '$last_week_date') AND vid_posttime <= '$last_week_timestamp' 
        GROUP BY hit_itemid 
        HAVING  total_hits < 2 

This does output the items that were access once in the last week…. but not the ones that haven't been accessed at all. If an item wasn't accessed at all in that last week, there wont be any entries in the daily_hits table. I figured COALESE should take care of that, but that didnt work.

How can I fix this?

Best Solution

 HAVING  total_hits < 2 or total_hits is null