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) AS Market_Value,
                      SUM(r_passet.Accrued_Income) AS  Accrued_Income
FROM         StyleToAssetCategory INNER JOIN
                      accounts ON StyleToAssetCategory.Style = accounts.Style INNER JOIN
                      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_Account
WHERE     (r_passet.Statement_Date = CONVERT(DATETIME, '12/1/2004', 102)) AND (CompositeMakeUp.Composite_ID = 675)
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?
                        
LVL 5
gigglickAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Try this:

SELECT Asset_Type, Security_Type, SUM(Book_Value) AS Book_Value, SUM(Market_Value) AS Market_Value,
SUM(Accrued_Income) AS Accrued_Income
FROM (
SELECT DISTINCT
                      r_passet.Asset_Type, r_passet.Security_Type, r_passet.Book_Value AS Book_Value, r_passet.Market_Value AS Market_Value,
r_passet.Accrued_Income AS  Accrued_Income
FROM         StyleToAssetCategory INNER JOIN
                      accounts ON StyleToAssetCategory.Style = accounts.Style INNER JOIN
                      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_Account
WHERE     (r_passet.Statement_Date = CONVERT(DATETIME, '12/1/2004', 102)) AND (CompositeMakeUp.Composite_ID = 675)) A
GROUP BY Asset_Type, Security_Type
0
 
rafranciscoCommented:
What's the output without the sum/group by AND without the DISTINCT clause?  Is it still the same or not?
0
 
gigglickAuthor Commented:
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)
0
 
gigglickAuthor Commented:
It worked!! It worked!! Thank you!! Makes sense looking at it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.