Summing on Parameters

Question

Summing on Parameters

TroyG

SSCrazy

Points: 2633
More actions
June 5, 2006 at 8:57 pm

#112497

Hi All,
Am wanting to do something relativly straight forward - or at least I thought it was - but I fear I am missing some vital peice of knowledge to allow me to get there.
Is it possible to use the parameters which are selected within a sum function on the select?
ie
Select Name, Sum(@param1)
from customer_order_lines
group by Name
In the above example, @param1 would be setup to representing different fields within the customer_order_lines table - for example total_amt, tax, discount
When the report is run it would show the customer name and the field which is selected in the parameter, summed.
In principal - within my head - this seems to be sort of sound. But I am getting an error saying:
"The sum or average aggregate operation cannot take a nvarchar data type as an argument"
Likewise if I run the query without the sum it just returns the value which I selected as the parameter (for example - Jones, total_amt), rather than the value that field should have in the table (for example - Jones, 2334.76)
Any thoughts?
Thanks in advance,
Troy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

I can think of two options:

1. You can use the IF... ELSE IF statement and repeat the SQL code that you've supplied for each requried column to sum.

2. You can use a dynamic SQL to build the required SQL code and then use EXEC () or sp_executesql to execute the SQL code.

Option 1 is more code intensive whereas dynamic SQL can introduce Stored Procedure recompiles.

TroyG SSCrazy Points: 2633 More actions · Answer 2

thanks for the response Paul - although I would have expected something easier (like certain switch/parenthisis/etc around the parameter to make it aggregate etc) - sort of amazes me that something like this cannot be done!?!

Thankfully the code is pretty straight forward and basic, so the IF....ELSE IF might be the ticket...

If anyone else has any other "nicer" ways to deal with this scenario (surely others have had similar situations?) it would be great to hear them.

Cheers

Troy

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

Oh, one other possibility is to use the CASE statement:

Select Name, SUM(CASE @param1 when 'colA' then colA when 'colB' then colB ... end) AS Total

from customer_order_lines

group by Name

Dave Klug SSCertifiable Points: 5811 More actions · Answer 4

Can you sum all fields in the report that the user can pick from the parameter and then use the IsHidden property to hide what they have not chosen?

TroyG SSCrazy Points: 2633 More actions · Answer 5

Hi David,

Funnily enough that is what I ended up doing...slightly changed the mindset of how I was going to achieve the end result - and also change the layout a tad too...but information set is the same, so the user is happy enough...

Cheers

Troy