CODEWITHSUNDEEP
sql
Ahmad Latif
Ahmad Latif

Reputation: 1

Multiplying two columns from two different tables

I'm trying to sum multiply two columns from two different tables but I keep getting different results. What I really want is to have the shares from the HoldingReport table to be only from a distinct CustomerServiceId. For example If CustomerServiceId = 2 then the shares should be multiplied with the current value of whatever stock they have and summed for only those columns with CustomerServiceId = 2.

These are the two tables:

enter image description here

enter image description here

And the query I have is:

SELECT SUM(CurrentValue*Shares) AS TotalAUM
FROM Stocks,HoldingReport
WHERE CustomerServiceId = '2'

Upvotes: 0

Views: 264

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Presumably, the query you want is:

SELECT SUM(s.CurrentValue * hr.Shares) AS TotalAUM
FROM Stocks s JOIN
     HoldingReport hr
     ON s.stocksid = hr.stocksid
WHERE hr.CustomerServiceId = 2;

Note that CustomerServiceId looks like a number, so I removed the single quotes. One use single quotes for string and date constants. (Of course, if it is really a string, then keep the single quotes!)

Upvotes: 1

Related Questions