On 07/08/2009 06:11 PM, Gary Smith wrote:
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.
I had to change month_name to month and add "AS foo" just before the
GROUP BY ("Every derived table must have its own alias") but still no
joy. I'm still getting those same incorrect numbers.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
