CODEWITHSUNDEEP
mysqljoinsum
Dave Driesmans
Dave Driesmans

Reputation: 819

SQL Query with join and sum

I want to get the sum of all invoices totals as well as the sum of all items (via quantity) ordered. When i add the join the sum is wrong and seems like things are counted double.

How can i solve this?

select
sum(invoices_items.item_quantity) as registration_count,
sum(invoices.total_incl_vat) as registration_price
from  invoices
inner join invoices_items on invoices_items.invoice_id = invoices.id;

Upvotes: 0

Views: 68

Answers (3)

Rajat
Rajat

Reputation: 5803

With your approach, you'll need to aggregate them first before you do the JOIN

SELECT  t.id, registration_price, registration_count
FROM 
(SELECT id, SUM(invoices.total_incl_vat) AS registration_price FROM invoices GROUP BY id) t
INNER JOIN
(SELECT  invoices_id, SUM(invoices_items.item_quantity AS registration_count FROM invoices_items GROUP BY invoices_id) tt 
ON tt.invoices_id = t.id
GROUP BY t.id WITH ROLLUP;

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

When you join each row of an invoice is as often in the result as it has items. That will cause the sum for the invoices to grow.

If you just want the two sums in one row you can use a SELECT without a FROM clause and subqueries.

SELECT (SELECT sum(item_quantity)
               FROM invoice_items) registration_count,
       (SELECT sum(total_incl_vat)
               FROM invoices) registration_price;

Upvotes: 3

Zeeshan Anjum
Zeeshan Anjum

Reputation: 974

it seems invoice items have more then one record against invoice so you can do like that

select
invoices.id,
(select sum(invoices_items.item_quantity from invoices_items where invoices_items.invoice_id = invoices.id) as registration_count,
sum(invoices.total_incl_vat) as registration_price
from  invoices
group by invoices.id

Upvotes: 0

Related Questions