I'm having this problem, and it really grinds my gears, because I really cannot find a solution. I'm working in SQL Server Management Studio. Here, I select distinct data and the average of another column's values for each distinct data, like this:
SELECT age, standing
FROM
(SELECT DISTINCT standing, avg(age) as age
FROM student
group by standing) as s
Now, this works fine. The problem occurs, when I try to do this:
SELECT age, standing
FROM
(SELECT DISTINCT standing, avg(age) as age
FROM student
group by standing) as s
EXCEPT
SELECT age, standing
FROM
(SELECT DISTINCT standing, avg(age) as age
FROM student
WHERE standing = 'JR'
group by standing) as k
The select above EXCEPT and below EXCEPT works fine, but together they just don't. The program tells me an error about the except: There was an error parsing the query. [ Token line number = 6,Token line offset = 1,Token in error = EXCEPT ]
I also tried UNION, INTERSECT and MINUS. Only union seemed to work, and for now I really don't know what's going on here.
I'm looking forward for the solution, and also really interested what am I doing wrong.
Are you trying to exclude records with 'JR' standing? Why not try this:
SELECT age, standing
FROM
(SELECT DISTINCT standing, avg(age) as age
FROM student
where standing <> 'JR'
group by standing) as s