List:General Discussion« Previous MessageNext Message »
From:b Date:July 9 2009 12:44am
Subject:Re: COUNT from 2 tables
View as plain text  
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.
Thread
COUNT from 2 tablesb8 Jul
  • RE: COUNT from 2 tablesGary Smith8 Jul
    • Re: COUNT from 2 tablesb8 Jul
      • RE: COUNT from 2 tablesGary Smith9 Jul
        • Re: COUNT from 2 tablesb9 Jul
  • Replication switch Master to slave and backBryan Cantwell8 Jul
    • Re: Replication switch Master to slave and backAaron Blew8 Jul
      • Re: Replication switch Master to slave and backMarcus Bointon8 Jul