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: Peter Brawley
Date: May 31, 2013 04:24PM

select distinct
  u.Year, u.Month, 
  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, count(u.id) as NewUsers
  from reportcal r
  left join user u on 100*r.yr+r.mo = yearmonth(u.registration_date)
  group by Year,Month
) as u 
join (
  select r.yr as Year, r.mo as Month, count(u.id) as TotalUsers
  from reportcal r
  left join user u on 100*r.yr+r.mo >= yearmonth(u.registration_date)
  group by year, month
) ucum using(Year,Month)
join (
  select r.yr as Year, r.mo as Month, count(distinct i.user_id) as ActiveUsers, count(i.invoice_number) as Invoices
  from reportcal     r
  left join invoices i on yearmonth(i.creation_date)=100*r.yr + r.mo
  group by year, month
) inv using(Year,Month)
order by Year,Month  ;

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
May 31, 2013 04:24PM


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.