Romulo Romulo - 1 month ago 6
MySQL Question

Adding totals from multiple tables

I'm using Laravel and Eloquent on MySQL. Essentially I am trying to get results from invoices, including their 'total' and 'paid' amounts.

I have 4 tables:

invoices

id int(10),
date_due date,
client_id int(10),
deleted_at datetime


invoices_items

id int(10),
invoice_id int(10),
price decimal(15,2),
quantity int(10)


invoices_payments (this is a pivot table as payments can apply to other invoices too)

payment_id int(10),
invoice_id int(10),
amount decimal(15,2)


payments

id int(10),
payment_date date,
total decimal(15,2)


(there are other fields but they are not relevant)

I've been using this query, based on a few other answers and other research:

select
`invoices`.*,
SUM(
invoices_items.price * invoices_items.quantity
) as total ,
SUM(
invoices_payments.amount
) as paid
from
`invoices`
left join `invoices_items` on `invoices`.`id` = `invoices_items`.`invoice_id`
left join `invoices_payments` on `invoices`.`id` = `invoices_payments`.`invoice_id`
where
`invoices`.`deleted_at` is null
limit
25


The problem I am having is that the result always only returns 1 row (there are 5 invoices in the test db), and the amount for 'total' or 'paid' is not correct.

I'd like to add that there may not be any records in
invoices_payments


-- SOLUTION --

Here is the final query in case anyone runs into similar situation

select
`invoices`.*,
SUM(
invoices_items.price * invoices_items.quantity
) as total,
IF(
invoices_payments.amount != null,
SUM(invoices_payments.amount),
0
) as paid,
SUM(
invoices_items.price * invoices_items.quantity
) - IF(
invoices_payments.amount != null,
SUM(invoices_payments.amount),
0
) as balance
from
`invoices`
left join `invoices_items` on `invoices`.`id` = `invoices_items`.`invoice_id`
left join `invoices_payments` on `invoices`.`id` = `invoices_payments`.`invoice_id`
where
`invoices`.`deleted_at` is null
group by
`invoices`.`id`
order by
`balance` desc
limit
25

Answer

Add a GROUP BY invoices.id after the WHERE

Comments