Aggragate function and other columns

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

Transact-SQL (2012)

Aggragate function and other columns

Author

Topic

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 09:34:00

Hi, I'm having trouble making this query work. Its supposed to show the average score of each class so that's why I have it grouped by class but I get an error saying the other columns need to be in group by as well but I don't want that and the results get messed up. I'm not sure how to structure it correctly when there is an aggregate function like this one. Here is the basics of my code

select  round(avg(cast(AvgeScore as float)),2) as 'Average Score',class, CurrentScore, Col2, Col3
from dbo.Results
group by Class

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-23 : 10:29:54

The rule is that anything that you have in the SELECT clause that is outside of an aggregate function MUST be in the GROUP BY clause. So either you should include CurrentScore, Col2, Col3 also in the GROUP BY clause, or remove those from the SELECT clause.

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 10:40:26

But I need all those columns to show. Should the avg be in a subquery? Or in a different clause?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 10:58:22

include them in the group by clause as JamesK said

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 11:20:57

Then the Average Score and Current Score come out the same.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 12:05:08

Then, that's what your data has.

Put this another way: If you don't group by class, CurrentScore, Col2, Col3, which aggregated row (there may be many) should SQL pull the values for those columns from?

Perhaps you should post some data and the results you want.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-23 : 16:31:40

Try this:


select class, CurrentScore, Col2, Col3,
    avg(AvgeScore) over(partition by class) AS 'AverageScore'
from dbo.Results

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 17:21:57

Works great! Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources