mysql - 如何对两个相关表中的列求和

Question

我有两张 table 。 发票和invoice_items。我正在尝试获取每个客户的 total_paid 和总 quantity 的总和。我尝试了这个查询:

SELECT client_id,
sum(total_amount), sum(it.quantity) as days_hired
FROM  `invoices` `iv`
  join invoice_items it on  it.invoice_id=iv.invoice_id
group by client_id, it.invoice_id

但是对于 client_id 14，我收到的总付款为 1908，而不是 636。看起来此列的总和对于每个 invoie_item 都会重复。任何帮助将不胜感激。

invoices

invoice_id    client_id    total_payment
    36        13           530
    38        14           636


invoice_items

invoice_id    user_id      quantity     
36            2            2
38            3            2
38            4            2
38            5            2

预期输出:

13     530       2
14     636       6

Answer 1

您可以尝试以下 -

SELECT client_id, sum(total_amount) as toal_amount, sum(it.quantity) as total_quantity
FROM  `invoices` `iv`
join
(
 select invoice_id,sum(quantity) as quantity from invoice_items group by invoice_id
)it on  it.invoice_id=iv.invoice_id
group by client_id, it.invoice_id