Aggregate function on a column supplied by a variable

Here is the dilemma.  I have a stored procedure where I need to grab the AVG of a column containing chemistry data.   There is a separate column for all chemistries we track, roughly 26 of them.  C, ZR, Pb, etc. etc.   This stored procedure needs to just do the avg on the chemistry that the user selects.

Is there a way to do this dynamically by passing the column name as a variable?

Below is a version that I attempted, but I get the error:

The name 'SELECT TOP 1 Avg[C] FROM XXXX GROUP BY Type' is not a valid identifier.
DECLARE @SQL2 varchar(1000)

BEGIN

DECLARE @in nvarchar(5)
SET @in = 'C'

SET @SQL2 = 'SELECT TOP 1 Avg['+@in+'] FROM XXXX GROUP BY Type'
EXEC @SQL2
END

Open in new window

hscastAsked:
Who is Participating?
 
Forefront_Data_SolutionsConnect With a Mentor Commented:
Use parentheses instead:  

SET @SQL2 = 'SELECT Avg('+@in+') FROM XXXX GROUP BY Type'

I also don't think you need "Top 1"
0
 
hscastAuthor Commented:
Nevermind.  Added ( after Avg and ) after the end bracket and it worked.
0
 
hscastAuthor Commented:
Whoops.  Quick answer Forefront!  I'll give you the points still.
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.