Except is not working with embedded sql query
<p>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:</p>

<pre><code>SELECT age, standing
FROM 
(SELECT DISTINCT standing, avg(age) as age
FROM student
group by standing) as s
</code></pre>

<p>Now, this works fine. The problem occurs, when I try to do this:</p>

<pre><code>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
</code></pre>

<p>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 ]</p>

<p>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.</p>

<p>I'm looking forward for the solution, and also really interested what am I doing wrong.</p>