从多个表中添加总计

Question

我在MySQL上使用Laravel和雄辩。本质上，我试图从发票中获得结果，包括发票的“总额”和“已支付”金额。

我有四张桌子：

发票

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 (这是一个支点表，因为付款也可以适用于其他发票)

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

付款

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

(还有其他领域，但它们并不相关)

我一直在使用这个查询，基于其他一些答案和其他研究：

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

我遇到的问题是，结果总是只返回1行(测试数据库中有5张发票)，“总计”或“已支付”的金额是不正确的。

我想补充一点，invoices_payments中可能没有任何记录

-解决办法

这是最后一个查询，以防任何人遇到类似的情况。

select 
    `invoices`.*, 
    COALESCE(SUM(
        invoices_items.price * invoices_items.quantity
    ),0) as total, 
    COALESCE(SUM(invoices_payments.amount),0) as paid, 
    COALESCE(SUM(
        invoices_items.price * invoices_items.quantity
    ),0) - COALESCE(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 1

在GROUP BY invoices.id之后添加WHERE