Solved

Aggregate function on a column supplied by a variable

Posted on 2011-03-01
3
204 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:hscast
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
Forefront_Data_Solutions earned 500 total points
ID: 35007461
Use parentheses instead:  

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

I also don't think you need "Top 1"
0
 

Author Comment

by:hscast
ID: 35007493
Nevermind.  Added ( after Avg and ) after the end bracket and it worked.
0
 

Author Comment

by:hscast
ID: 35007496
Whoops.  Quick answer Forefront!  I'll give you the points still.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now