Solved - Group By and Order By Question

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 2000 Forums

Transact-SQL (2000)

Solved - Group By and Order By Question

Author

Topic

waskelton4
Starting Member

12 Posts

Posted - 2005-01-27 : 14:04:39

Hello all..

here is my question..

I have some sql..


SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText 
FROM fields_evaluation as fe
INNER JOIN eval_data AS d  ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON fe.intField_ID = f.intField_ID 
WHERE fe.intAverage = '1' 
AND d.intCustom = '15'
AND fe.intEval_ID = '1'
Group By f.chEvalFieldText

My problem is that It is ordering the results by the FieldText var but I would actually like it to order it by the id associated with that var.. such as..


SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText 
FROM fields_evaluation as fe
INNER JOIN eval_data AS d  ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON fe.intField_ID = f.intField_ID 
WHERE fe.intAverage = '1' 
AND d.intCustom = '15'
AND fe.intEval_ID = '1'
Group By f.chEvalFieldText
Order By f.intField_ID

but it tells me I can't because intField_ID isn't in an aggregate function

Column name 'f.intField_ID' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

How can I accomplish this?

any Ideas?

Thanks
Will

BAD SPELLERS OF THE WORLD UNTIE!!

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-01-27 : 14:30:21

[code]
SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText , f.intField_ID
FROM fields_evaluation as fe
INNER JOIN eval_data AS d ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON fe.intField_ID = f.intField_ID
WHERE fe.intAverage = '1'
AND d.intCustom = '15'
AND fe.intEval_ID = '1'
Group By f.chEvalFieldText, f.intField_ID
Order By f.intField_ID
[/code]

waskelton4
Starting Member

12 Posts

Posted - 2005-01-27 : 14:35:50

Pefect..

Thanks..

ws

BAD SPELLERS OF THE WORLD UNTIE!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources