help modifying query - SQL Server Forums

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 modifying query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-05 : 21:30:44

Hi,

I have the following query that works perfectly, but I am looking to modify it.


SELECT questionid, cast(len(response) as float)/750.0*100 as averageFillPercentage,response as averageCharacters
FROM tblQuestions 
WHERE userID = 500

I would like to change it to a GROUP BY on question ID and drop the WHERE clause, but I get an error when running this.


SELECT questionid, cast(len(response) as float) / 750.0*100 as averageFillPercentage 
FROM tblQuestions 
GROUP BY questionID

Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblQuestions.response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any help is much appreciated!

thanks again,
mike123

(table structure)

CREATE TABLE [dbo].[tblQuestions](
[userID] [int] NOT NULL,
[questionID] [tinyint] NOT NULL,
[response] [varchar](750) NULL
)

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-05 : 21:57:37

Don't you want to get Average Fill Percentage?
Acerage is something like TOTAL/COUNT.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-05 : 22:01:44

if you are using a group by, all columns that in the select statment MUST be grouped on, or contained in aggregate functions i.e. MAX,MIN,SUM,COUNT,etc.

The column response is in neither that's why you get a error,

Can you give some sample data and desired results to clarify.

I can't tell for certain if this is what you want, but if you are looking to take the average value of that specific questionID, you can do a query like so assuming your calculation is accurate.

SELECT questionid, cast(avg(len(response)) as float) / 750.0*100 as averageFillPercentage
FROM tblQuestions
GROUP BY questionID

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-05 : 22:14:12

Hey Vinnie881,

Your query seems to be working!

Thanks so much :)
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources