Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum problem

Status
Not open for further replies.

KurtMillikin

Programmer
Joined
Feb 21, 2003
Messages
7
Location
US
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top