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.
