How can I fix this mysql query?

Question

I have a query as follows:

SELECT  website_url, 
        website_id, 
        website_shady, 
       (sum(dstats_hits)+website_in_ballast) as total_in, 
       (sum(dstats_hits_out)+website_out_ballast) as total_out, 
        sum(dstats_hits_out_paid) as total_paid, 
        website_in_ballast, 
        website_out_ballast 
FROM websites 
LEFT JOIN domain_stats 
          ON (
               websites.website_id = domain_stats.dstats_domid 
               AND dstats_date >= '$ratio_start'
             ) 
WHERE website_userid = $id
GROUP BY website_url 
ORDER BY website_url

The problem is with the total_in and total_out aliases. If the sum(dstats_hits) doesn't have anything to sum (because there are no records in the domain_stats table), it will not add the website_in_ballast value, and the alias returns blank, even though website_in_ballast has a value (which is a float). If there are records to sum, it functions perfectly.

I could solve this by adding the alias in php to the sum value (which could and could not be 0), but in another example I cannot do that, so Im looking for a mysql only solution.

Answer 1

Maybe try to reverse it, so that you have

website_in_ballast + sum(dstats_hits)

Answer 2

coalesce(sum(dstats_hits),0) + ...

Answer 3

Can you use COALESCE() to fix the null sum?

SELECT website_url, website_id, website_shady,
       COALESCE(sum(dstats_hits), 0) + website_in_ballast as total_in,
       COALESCE(sum(dstats_hits_out), 0) + website_out_ballast as total_out,
       sum(dstats_hits_out_paid) as total_paid,
       website_in_ballast, website_out_ballast 
  FROM websites LEFT JOIN domain_stats
          ON (websites.website_id = domain_stats.dstats_domid AND
              dstats_date >= '$ratio_start') 
  WHERE website_userid = $id
  GROUP BY website_url 
  ORDER BY website_url;

Untested SQL!