Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period

[Peter Brawley]

To clarify the basic logic, materialise the Group By columns ...

drop table if exists base;
create table base
  select yr,mo,postalcode
  from reportcal
  cross join city
  where 100*yr+mo between 201201 and 201305;

The eventual query result will have reportcal.rowcount * city.rowcount rows.

Now the first subquery becomes ...

  select b.yr as Year, b.mo as Month, b.postalcode, count(u.id) as NewUsers 
  from base b 
  left join user u  on 100*b.yr+b.mo = yearmonth(u.registration_date)  and b.postalcode=u.postalcode
  left join user uu on 100*b.yr+b.mo = yearmonth(uu.registration_date) and b.postalcode=uu.postalcode
  where 100*b.yr+b.mo between 201203 and 201305 
  group by postalcode,Year,Month

You can work out the rest from there.