Error with Select list and Exists

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)

Error with Select list and Exists

Author

Topic

giszzmo
Starting Member

29 Posts

Posted - 2015-01-23 : 17:06:07

Hi, I'm having trouble figuring out how to fix this query. I get the error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

select  CurrentGrade, class, Semester, convert(date, RunDate) 'Run Date',
	(
	select  round(avg(cast(CurrentGrade as float)),2) as 'Average Grade', class
	from GradeResults
	where convert(date,RunDate) = (select MAX(convert(date,RunDate))'Run Date' from GradeResults)
	group by Class, RunDate
	) as 'Average'
	
	from GradeResults
	where convert(date,RunDate) = (select MAX(convert(date,RunDate))'Run Date' from GradeResults) 
	group by Class, Semester, CurrentGrade, RunDate

If anyone could point me in the right direction. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-23 : 17:20:38

I'm confused what you are trying to achieve. Please show us sample data and expected result set.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-28 : 02:56:22

The Subquery returns more than one result for each row from main table. Please restructure the query. I have altered query try this. It may work

select CurrentGrade, class, Semester, convert(date, RunDate) 'Run Date',
(
select round(avg(cast(CurrentGrade as float)),2) as 'Average Grade', class
from GradeResults b
where convert(date,RunDate) = convert(date,b.RunDate)
and a.Class = b.Class
group by Class, RunDate
) as 'Average'

from GradeResults a
where convert(date,RunDate) = (select MAX(convert(date,RunDate))'Run Date' from GradeResults)
group by Class, Semester, CurrentGrade, RunDate

Regards
Viggneshwar A

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 07:13:25

another homework question!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources