MySQL Forums
Forum List  »  General

Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period
Posted by: Radoslav Yankov
Date: June 04, 2013 11:06AM

It doesn't work correctly. I don't know how to bind the last subquery with the CITY table which contains the postal codes.
Can you see what I'm doing wrong, please.

select distinct
u.Year, u.Month, u.postalcode,
ifnull(u.NewUsers,0) as NewUsers,
ifnull(ucum.TotalUsers,0) as TotalUsers,
ifnull(inv.Activeusers,0) as ActiveUsers,
ifnull(inv.Invoices,0) as Invoices
from (
select r.yr as Year, r.mo as Month, ct.postalcode, count(u.id) as NewUsers
from reportcal r
cross join city ct
left join user u on 100*r.yr+r.mo = yearmonth(u.registration_date)
left join user uu on ct.postalcode = uu.postalcode
where 100*r.yr+r.mo between 201203 and 201304
group by postalcode,Year,Month
) as u
join (
select r.yr as Year, r.mo as Month, ct.postalcode, count(u.id) as TotalUsers
from reportcal r
cross join city ct
left join user u on 100*r.yr+r.mo >= yearmonth(u.registration_date)
left join user uu on ct.postalcode = uu.postalcode
group by postalcode,year, month
) ucum using(postalcode,Year,Month)
join (
select r.yr as Year, r.mo as Month, ct.postalcode,count(distinct i.user_id) as ActiveUsers, count(i.invoice_number) as Invoices
from reportcal r
cross join city ct
left join invoices i on yearmonth(i.creation_date)=100*r.yr + r.mo
group by postalcode,year, month
) inv using(postalcode,Year,Month)
order by Year,Month, postalcode ;

Options: ReplyQuote


Subject
Written By
Posted
Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period
June 04, 2013 11:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.