Create a view or sub select, denormalizing the data and then group it. select month, sum(login) as num_logins, sum(download) as num_downloads from ( select monthname(s.created) as month_name , if(ifnull(s.id, 0) > 0, 1, 0) as login , if(ifnull(d.id, 0) > 0, 1, 0) as download from sessions s left join downloads d on s.id = d.session_id ) group by month name;
which is the left table? downloads or logins? If logins shouldn't it be on the left side of the ON statement? (I'm not sure) My understanding is that is the ON statement that runs the left join, not which table is listed first (I could be wrong though -- chime in if you know the definitive answer please). Anyway, try this and see if it gets you closer. ________________________________________ From: b [my...@logi.ca] Sent: Wednesday, July 08, 2009 12:55 PM To: mysql@lists.mysql.com Subject: Re: COUNT from 2 tables On 07/08/2009 03:33 PM, Gary Smith wrote: > Off the top of my head, try this. > > SELECT > MONTHNAME(s.created) AS month, > sum(if(ifnull(s.id,0)> 0, 1, 0)) AS num_logins, > sim(if(ifnull(d.id, 0)> 0, 1, 0)) AS num_downloads > FROM sessions AS s LEFT JOIN downloads AS d > ON d.session_id = s.id GROUP BY month > Nope, I'm still getting those same incorrect sums. Thanks, though. It seems to me that the problem is that I'm grouping by the month for one table but counting from both. I'd paste the output here but I just upgraded Fedora and the BETA (wtf?) version of Thunderbird crashes when I paste into an email (how the earlier paste worked I don't know). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
