[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem with Sum function

Posted on 2005-04-18
4
Medium Priority
?
202 Views
Last Modified: 2010-03-19
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?
                        
0
Comment
Question by:gigglick
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13809890
What's the output without the sum/group by AND without the DISTINCT clause?  Is it still the same or not?
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 800 total points
ID: 13809914
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
 
LVL 5

Author Comment

by:gigglick
ID: 13809919
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
 
LVL 5

Author Comment

by:gigglick
ID: 13809929
It worked!! It worked!! Thank you!! Makes sense looking at it.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question