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

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 07:13:25
another homework question!
Go to Top of Page
   

- Advertisement -