CODEWITHSUNDEEP
sql
krychuq
krychuq

Reputation: 445

Multiply two columns and sum them on inner join

I have two tables and I need to get the sum of a.TOTAL * b.QUANTITY.

a (A_ID, TOTAL)

b (B_ID, QUANTITY)

So far I wrote:

SELECT a.A_ID, a.TOTAL * b.QUANTITY as calculation
FROM a INNER JOIN b ON 
a.A_ID = b.B_ID

I tried ...SUM(a.TOTAL * b.QUANTITY) as calculation but it doesn't work. I would be grateful for any help!

Upvotes: 1

Views: 3343

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

Did you intend to do a grouping on A's id. If so, then this might give your desired result:

SELECT a.A_ID,
       SUM(a.TOTAL * b.QUANTITY) AS calculation
FROM a
INNER JOIN b
    ON a.A_ID = b.B_ID
GROUP BY a.A_ID

Upvotes: 2

Bruno
Bruno

Reputation: 4665

Try this :

SELECT A_ID, SUM(calculation) as mySum
FROM (
    SELECT a.A_ID, a.TOTAL * b.QUANTITY as calculation
    FROM a INNER JOIN b ON 
    a.A_ID = b.B_ID
) q
GROUP BY A_ID

Upvotes: 4

Related Questions