mysql query: show summary of all payments made and total of invoices by date

I want this kind of result

from these tables.

I even can’t figure out how to do it with php. I even tried to join payment and invoice table on date but in vain.

It’s a purchase system and this query will show summary of all payments made and total of invoices by date.

I thought of a solution that first select all dates from invoices and then select all dates from payments and take their union. Then check if there is an invoice on that date and then check if there is a payment on that date. But this way there will be too many queries.

Answer

select
  x.date,
  sum(x.invoiceTotal) as invoiceTotal,
  sum(x.paymentsMade) as paymentMade
from
 (select
   i.date, 
   sum(i.rate * i.quantity /*?*/) as invoiceTotal,
   null as paymentMade    
 from
   invoice i
   inner join invoiceitem ii on ii.invoiceId = i.invoiceId
 group by
   i.date
 union all
 select
   p.date,
   null as invoiceTotal,
   sum(p.amount) as paymentMade    
 from
   payment p
 group by
   p.date) x
group by
  x.date
order by
  x.date