只需“联合”两个表以及invoice\u product和invoice\u support，因为当前查询发现invoice\u id在所有三个表中都是通用的（invoice，invoice\u product，invoice\u support）：

select c.id, c.name, count (inv_prod_support.id_invoice), sum(inv_prod_support*quantity) as total_products,
sum(inv_prod_support.value) as total_support from
(select id_invoice, value, 0 as quantity from invoice_support
union select id_invoice, value, quantity from invoice_product ) inv_prod_support, invoice i
inner join customer c on i.id_customer = c.id
inner join inv_prod_support on inv_prod_support.id_invoice = i.id_invoice
group by c.id, c.name
order by total_amount, total_products desc

我认为你必须使用一个独特的计数，也总和产品和支持。

SELECT 
    c.id, 
    c.name, 
    COUNT(DISTINCT i.id) as invoices_count, 
    SUM(ip.value * ip.quantity) + SUM(is.value) as total_amount
FROM Invoice i
INNER JOIN Costumer c ON i.id_costumer = c.id
LEFT JOIN InVoice_Product ip ON ip.id_invoice = i.id
LEFT JOIN InVoice_Support sp ON sp.id_invoice = i.id
GROUP BY c.id, c.name
ORDER BY invoices_count, total_amount DESC

试试看

我认为问题在于对可能为空的值进行数学运算。试一试：

SELECT
    c.id, 
    c.name, 
    COUNT(i.id) as total_amount, 
    SUM(IF(NOT ip.value IS NULL,ip.value * ip.quantity,0)) as total_products,
    SUM(IF(NOT is.value IS NULL,is.value,0)) as total_support
FROM Invoice i
INNER JOIN Costumer c ON i.id_costumer = c.id
LEFT JOIN InVoice_Product ip ON ip.id_invoice = i.id
LEFT JOIN InVoice_Support sp ON sp.id_invoice = i.id
GROUP BY c.id, c.name
ORDER BY total_amount, total_products DESC