Help w/ using Variables in Aggregate Functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help w/ using Variables in Aggregate Functions

Author

Topic

rrathsam
Starting Member

3 Posts

Posted - 2007-08-13 : 09:41:19

Hi all,

I've been searching around the forums and the net in general and haven't found much of anything that might help me figure out what the source of the problem I'm running into.

Basically I'm trying to write a script that will populate a table with values derived from other tables, one of these values is the AVG of some columns.

For some setup information:
Tables:
Data (Contains the columns I'm trying to AVG)
Averages (Table that I'm populating)
Questions (Table I'm pulling the list of columns that I'm going to
be getting from Data)

What I'm looking to end up with:
I'm looking to end up with a Table that displays this information...
Question(Q#),Average

Here are the lines of I'm having problems with:

INSERT INTO Averages (ID,Average)
Select col_Name(Object_ID(@Table_ID),@Col_ID), ROUND(AVG(@Column_ID),1)
FROM Data

The error I get is:
Msg 8114, Level 16, State 5, Line 40
Error converting data type varchar to float.

Any help would be greatly appreciated :)

Thanks in advance!

Ryan

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 09:52:33

can you post your table structure and some sample data

using a simple convert to float from varchar might solve your problem.

Ashley Rhodes

rrathsam
Starting Member

3 Posts

Posted - 2007-08-13 : 10:06:19

Hi Ashley,

Here's my table structure:

Data:
ID - int
Q1 - float
Q2 - float
...
Q8 - float
Q12 - float

Questions:
ID(contents = Q1,Q2 etc...) - nvarchar
nID - int
Question - nvarchar

I actually tried out a convert and a cast to try and get the data to go, but it keeps spiting that error back at me. Like I had said I haven't found much but it seems to be something about using a variable in an aggregate function... not sure why this would be the case but I guess that's why I'm here :) Any ideas would be much appreciated.

Thanks again!

quote:
Originally posted by ashley.sql

can you post your table structure and some sample data

using a simple convert to float from varchar might solve your problem.

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 11:39:33

you did not post any data?

Ashley Rhodes

rrathsam
Starting Member

3 Posts

Posted - 2007-08-13 : 11:55:35

Woops, heh... sorry 'bout that.

Here's some sample data
Data:
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
8 4 3 4 4 4 4 4 4
9 3 4 4 4 4 4 3 4
10 4 0 0 0 0 0 4 0

Questions:
ID nID Question
Q1 1 {Insert Question Here}
Q2 2 {Insert another question here}
etc..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources