gigglick
asked on
Problem with Sum function
Hello -
I am trying to run the following query
SELECT DISTINCT
r_passet.Asset_Type, r_passet.Security_Type, SUM(r_passet.Book_Value) AS Book_Value, SUM(r_passet.Market_Value)
SUM(r_passet.Accrued_Incom
FROM StyleToAssetCategory INNER JOIN
accounts ON StyleToAssetCategory.Style
r_passet ON accounts.Portfolio_ID = r_passet.Portfolio_ID AND accounts.Account_ID = r_passet.Account_ID INNER JOIN
CompositeMakeUp ON accounts.Account_ID = CompositeMakeUp.Component_
WHERE (r_passet.Statement_Date = CONVERT(DATETIME, '12/1/2004', 102)) AND (CompositeMakeUp.Composite
GROUP BY r_passet.Asset_Type, r_passet.Security_Type
The three sum outputs give me six times the value of what should appear. Sure it's something simple I just overlooked.
Without the sum/group by here is the output
Asset_Type Security_Type Book_Value Market_Value Accrued_Income
C Cs 0 0 0
E LM 9230302.89 10857557.59 10207.5
E LM 14889351.05 13459136.31 0
E LM 15661901.88 21547943.26 11386.75
E SC 5629123.09 8106488 2763.5
E SC 5826983.86 7558865.73 1599.54
ST Cs 0 0 0
ST Cs 106211.18 106211.18 204.73
ST Cs 208721.58 208721.58 227.7
ST Cs 455895.69 455895.69 875.9
ST Cs 786071.84 786071.84 1231.87
When I add the sum to get the aggregate its way off
Asset_Type Security_Type Expr1 Expr2 Expr3
C Cs 0 0 0
E LM 253578685.97 288646959.27 129565.5
E SC 68736641.7 93992122.38 26178.24
ST Cs 9341401.74 9341401.74 15241.2
Any ideas?
I am trying to run the following query
SELECT DISTINCT
r_passet.Asset_Type, r_passet.Security_Type, SUM(r_passet.Book_Value) AS Book_Value, SUM(r_passet.Market_Value)
SUM(r_passet.Accrued_Incom
FROM StyleToAssetCategory INNER JOIN
accounts ON StyleToAssetCategory.Style
r_passet ON accounts.Portfolio_ID = r_passet.Portfolio_ID AND accounts.Account_ID = r_passet.Account_ID INNER JOIN
CompositeMakeUp ON accounts.Account_ID = CompositeMakeUp.Component_
WHERE (r_passet.Statement_Date = CONVERT(DATETIME, '12/1/2004', 102)) AND (CompositeMakeUp.Composite
GROUP BY r_passet.Asset_Type, r_passet.Security_Type
The three sum outputs give me six times the value of what should appear. Sure it's something simple I just overlooked.
Without the sum/group by here is the output
Asset_Type Security_Type Book_Value Market_Value Accrued_Income
C Cs 0 0 0
E LM 9230302.89 10857557.59 10207.5
E LM 14889351.05 13459136.31 0
E LM 15661901.88 21547943.26 11386.75
E SC 5629123.09 8106488 2763.5
E SC 5826983.86 7558865.73 1599.54
ST Cs 0 0 0
ST Cs 106211.18 106211.18 204.73
ST Cs 208721.58 208721.58 227.7
ST Cs 455895.69 455895.69 875.9
ST Cs 786071.84 786071.84 1231.87
When I add the sum to get the aggregate its way off
Asset_Type Security_Type Expr1 Expr2 Expr3
C Cs 0 0 0
E LM 253578685.97 288646959.27 129565.5
E SC 68736641.7 93992122.38 26178.24
ST Cs 9341401.74 9341401.74 15241.2
Any ideas?
What's the output without the sum/group by AND without the DISTINCT clause? Is it still the same or not?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The output without the sum/group is above, removing the distinct on the sum/group portion with give duplicates, removing with the sum/group gives the same output (second output listed above)
ASKER
It worked!! It worked!! Thank you!! Makes sense looking at it.