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.
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 11:20:57
Then the Average Score and Current Score come out the same.
Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 17:21:57
Works great! Thank you so much!
Go to Top of Page
   

- Advertisement -