CODEWITHSUNDEEP
sqlmysql
mysterious
mysterious

Reputation: 1478

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

I want this kind of result alt text

from these tables.

alt text alt text alt text

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.

Upvotes: 2

Views: 1423

Answers (3)

mysterious
mysterious

Reputation: 1478

    select
  x.date,
  sum(x.invoiceTotal) as invoiceTotal,
  sum(x.paymentMade) as paymentMade
from
(SELECT
Sum((invoiceitem.rate * invoiceitem.quantity)) AS invoiceTotal,
invoice.`date`,
null AS paymentMade
FROM
      invoice
      Left Join invoiceitem ON invoice.invoiceId = invoiceitem.invoiceId
where companyId='6'
GROUP BY
      invoice.`date`
UNION ALL
SELECT
null as invoiceTotal,
  payment.date,
  sum(payment.amount) as paymentMade    
 from
  payment
 group by
  payment.date) x
group by
  x.date
order by
  x.date

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

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

Upvotes: 2

johnny
johnny

Reputation: 19735

You need to look at your joins. Look at this website:

http://www.wellho.net/solutions/mysql-mysql-joins-using-left-join-and-right-join-to-find-orphan-rows.html

I believe you should also look here because you want the joins to occur simultaneiously:

http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

Use your aggregates as appropriate.

Upvotes: 0

Related Questions