Pfthb

I have these tables : customers, customer_invoices, customer_invoice_details, each customer has many invoices, and each invoice has many details.

The customer with the ID 574413 has these invoices :

select customer_invoices.customer_id,
 customer_invoices.id,
 customer_invoices.total_price
from customer_invoices
where customer_invoices.customer_id = 574413;

result :

customer_id invoice_id total_price
574413 662146 700.00
574413 662147 250.00

each invoice here has two details (or invoice lines) :

first invoice 662146:

select customer_invoice_details.id as detail_id,
 customer_invoice_details.customer_invoice_id as invoice_id,
 customer_invoice_details.total_price as detail_total_price
from customer_invoice_details
where customer_invoice_details.customer_invoice_id = 662146;

result :

detail_id invoice_id detail_total_price
722291 662146 500.00
722292 662146 200.00

second invoice 662147 :

select customer_invoice_details.id as detail_id,
 customer_invoice_details.customer_invoice_id as invoice_id,
 customer_invoice_details.total_price as detail_total_price
from customer_invoice_details
where customer_invoice_details.customer_invoice_id = 662147;

result :

detail_id invoice_id detail_total_price
722293 662147 100.00
722294 662147 150.00

I have a problem with this query :

select customers.id as customerID,
 customers.last_name,
 customers.first_name,
 SUM(customer_invoices.total_price) as invoice_total,
 SUM(customer_invoice_details.total_price) as details_total
from customers
 join customer_invoices
 on customer_invoices.customer_id = customers.id
 join customer_invoice_details
 on customer_invoice_details.customer_invoice_id = customer_invoices.id
where customer_id = 574413;

unexpected result :

customerID last_name first_name invoice_total details_total
574413 terry amine 1900.00 950.00

I need to have the SUM of the total_price of the invoices, and the SUM of the total_price of the details for each customer. In this case I'm supposed to get 950 as total_price for both columns (invoice_total& details_total) but it's not the case. what am I doing wrong & how can I get the correct result please. The answers in similar topics don't have the solution for this case.

When you mix normal columns with aggregate functions (for example SUM), you need to use GROUP BY where you list the normal columns from the SELECT.

The reason for the excessive amount in total_price for invoices is that the SUM is also calculated over each detail row as it is part of the join. Use this:

select c.id as customerID,
 c.last_name,
 c.first_name,
 SUM(ci.total_price) as invoice_total,
 SUM((select SUM(d.total_price)
 from customer_invoice_details d
 where d.customer_invoice_id = ci.id)) as 'detail_total_price'
from customers c
 join customer_invoices ci on ci.customer_id = c.id
where c.id = 574413
group by c.id, c.last_name, c.first_name

db-fiddle

I used join against sub queries and then did a sum on the sums

SELECT c.id as customerID,
 c.last_name,
 c.first_name
 SUM(i.sum) as invoice_total,
 SUM(d.sum) AS details_total
FROM customers c
JOIN (SELECT id, customer_id, SUM(total_price) AS sum
 FROM customer_invoices
 GROUP BY id, customer_id) AS i ON i.customer_id = c.id
JOIN (SELECT customer_invoice_id as id, SUM(total_price) AS sum
 FROM customer_invoice_details 
 GROUP BY customer_invoice_id) AS d ON d.id = i.id
WHERE c.id = 574413
GROUP BY c.id, c.name

The issue is in the joining logic. The table customers is used as the driving table in the joins. But in the second join, you are using a derivative key column from the first join, to join with the third tables. This is resulting in a Cartesian output doubling the records from the result from the nth-1 join, which is leading to customer_invoices.total_price getting repeated twice, hence the rolled up value of this field is doubled.
At a high level I feel that the purpose of rolling up the prices is already achieved in SUM(customer_invoice_details.total_price).
But if you have a specific project requirement that SUM(customer_invoices.total_price) should also be obtained and must match with SUM(customer_invoice_details.total_price), then you can do this:
In a separate query, Join customer_invoice_details and customer_invoices. Roll up the pricing fields, and have a result such that you have only one record for one customer ID.
Then use this as a sub-query and join it with the customers table.

You are aggregating along multiple dimensions. This is challenging. I would suggest doing the aggregation along each dimension independently:

select c.id as customerID, c.last_name, c.first_name,
 ci.invoice_total,
 cid.details_total
from customers c join
 (select ci.sum(ci.total_price) as invoice_total
 from customer_invoices ci
 group by ci.customer_id
 ) ci
 on ci.customer_id = c.id join
 (select ci.sum(cid.total_price) as details_total
 from customer_invoices ci join
 customer_invoice_details cid
 on cid.customer_invoice_id = ci.id
 group by ci.customer_id
 ) cid
 on cid.customer_id = c.id
where c.id = 574413;

A faster version (for one customer) uses correlated subqueries:

select c.id as customerID, c.last_name, c.first_name,
 (select ci.customer_id, sum(ci.total_price) as invoice_total
 from customer_invoices ci
 where ci.customer_id = c.id 
 ) as invoice_total,
 (select ci.customer_id, sum(cid.total_price) as details_total
 from customer_invoices ci join
 customer_invoice_details cid
 on cid.customer_invoice_id = ci.id
 where ci.customer_id = c.id
 ) as details_total
from customers c 
where c.id = 574413;