CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012
Jay C
Jay C

Reputation: 872

Sum a column pulled from a Stored Procedure

Using SQL Server 2012

I have a query that pulls derived and dynamic data from a stored procedure then joins various views to it. The query is here.

https://dl.dropboxusercontent.com/u/29851290/cashpercent.sql

I added the following line to my query expecting this to pull the summed result of one of my columns, PercentAssets.

Line 21 of query

SUM(a.PercentAssets) AS SummedPCT

However SummedPCT pulls the exact same result as the non summed column PercentAssets.

Query1

What I want is SummedPCT to return results shown here in DesiredResult

Query2

The end result of all this is to use this query in a dataset and to filter on the summed column.

I tried to accomplish this through the rdl layer or a different type of query a few days ago when I asked the following question.

Filtering based on SSRS total/sum

I'm now thinking I just need to accomplish this somehow in the SQL Query itself and create a field that is the summed result of PercentAssets than I can filter based on it.

Thank you,

Upvotes: 1

Views: 1924

Answers (1)

Rich Andrews
Rich Andrews

Reputation: 4188

Your problem comes from the fact you are still grouping by the field a.PercentAssets. You cant have an aggregate over a column you have grouped by. To find the SUM of that field you will need to use a sub query

Upvotes: 1

Related Questions