b3tac0d3
b3tac0d3

Reputation: 908

Calculating the sum of quantity * unit price in mysql

I think I'm missing a simple step here but I can't seem to figure it out. I've read the other threads and they talk about grouping but I can't seem to put it all together right.

I have a simple table that holds inventory transactions. In each row, there is a quantity and a price. I want to get the sum of the quantity and the sum of the each price * each quantity.

Here's my query. If I remove the grouping, I get 1 result that is multiplied by the number of rows in the table. If I add the grouping, I get the correct result multiple times. Am I missing something here? I just feel like running a query to get 20k results when they all contain the same data would be pointless.

SELECT (SUM(i.quantity) - IFNULL(SUM(s.quantity), 0)) AS quantity,
SUM(i.unitprice * i.quantity) AS totalprice

FROM 02_01_transactions t

LEFT JOIN 02_01_transactions i
ON i.type = 1
AND i.active = 1

LEFT JOIN 02_01_transactions s
ON s.type = 2
AND s.active =1

GROUP BY t.id

Upvotes: 1

Views: 3541

Answers (2)

shawnt00
shawnt00

Reputation: 17925

Here's my guess at what you were trying to accomplish:

select
    sum(quantity * case type when 1 then 1 when 2 then -1 end) as quantity,
    sum(unitprice * quantity) as totalprice
from 02_01_transactions
where type in (1, 2) and active = 1

Upvotes: 1

JRD
JRD

Reputation: 1987

Not sure there is a need for the joins (you are not joining on any common value) or the type = 2 rows if you are just subtracting them out. Is there a reason the following does not work?

-- Total quantity, total price of all type 1, active transactions.

SELECT SUM(quantity) AS quantity,
       SUM(unitprice * quantity) AS totalprice
FROM   02_01_transactions
WHERE  type   = 1
AND    active = 1

Upvotes: 2

Related Questions