Sum problem

[KurtMillikin]

I need get a sum of transactions (shares) then multiply it by a price from a specific date then multiply (shares * price) AS CASH. I tried the following just to even get the sum of shares and a price.

I wind up with a dataset that contains multiple rows for the same fund (I want a single entry fund - price - sum(shares))

What can I do to fix that and can I then multiply shares * price and come up with the value (cash) in the same query?

SELECT PriceFile.PRICE , shares.COMP_NO, SUM(shares.SHARES) AS SHARES, Lu_Funds.FUND_NAME, Lu_Funds.FUND_CODE
FROM shares
INNER JOIN PriceFile ON shares.FUND = PriceFile.FUND_NO
INNER JOIN Lu_Funds ON PriceFile.TICKER = Lu_Funds.FUND_CODE
GROUP BY shares.COMP_NO, PriceFile.PRICE, PriceFile.CLOSE_DATE, Lu_Funds.FUND_NAME, Lu_Funds.FUND_CODE
HAVING (PriceFile.CLOSE_DATE = @dtTxDate) AND (shares.COMP_NO = @strCompNo)

Obvioulsy I'm new to this. Thanks very much to anyone that can help.

Kurt

 

[zhavic]

I assume, that you have only one PRICE for each day and also 1 row in 'Lu_Funds' for this PRICE

SELECT MAX(PriceFile.PRICE) AS PRICE ,
MAX(shares.COMP_NO) AS COMP_NO,
SUM(shares.SHARES) AS SHARES,
MAX(Lu_Funds.FUND_NAME) AS FUND_NAME,
MAX(Lu_Funds.FUND_CODE) AS FUND_CODE
FROM shares
INNER JOIN PriceFile ON shares.FUND = PriceFile.FUND_NO
INNER JOIN Lu_Funds ON PriceFile.TICKER = Lu_Funds.FUND_CODE
WHERE (PriceFile.CLOSE_DATE = @dtTxDate) AND (shares.COMP_NO = @strCompNo)


( The MAX() aggregated function returns right data, because
only one row (but myltiple times) is joined from 'PriceFile' and 'Lu_Funds' tables )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.