Repeated use of an alias within a join

I am trying to append a column created by using COUNT(*) and GROUP BY to the original sele开发者_如何学Pythonction that was counted. However, the selection is pretty complex (much more than the WHERE ... line I've included in my example) so I'd rather not duplicate the code.

SQL Server doesn't approve of my using an alias t1 inside of the left join statement. Any suggestions?

select t1.school, t1.grade,t1.individual,t2.cnt as 'class size' from (
        select * from students
        where (students.age < 16 and students.ACT_score is not null)
) as t1
left join (
    select distinct school, grade, count(*) as 'cnt' from t1
    group by school, grade
) as t2
on t1.school = t2.school and t1.grade = t2.grade

If it's 2005 or newer, use a CTE:

;WITH MyCTE AS
(
<Your complicate query here>
)
SELECT fields
FROM MyCTE
JOIN (subquery referencing MyCTE)
...

It might be easier to maintain if you use COUNT(*) with an OVER clause as follows:

with cntAppended as (
  select
    *,
    count(*) over (partition by school, grade)
  from students
)
  select
    school, 
    grade,
    individual,
    cnt as "class size"
from cntAppended
where (age < 16 and ACT_score is not null)

Don't be tempted to avoid WITH and putting the WHERE clause in one query with COUNT. If you do, you will only count students in each school and grade who are < 16 and have ACT scores. It looks like you want to count all students for the [class size] column, but only see data for certain ones in your result.

If and when T-SQL supports the QUALIFY keyword, queries like this may be even easier:

select
  school, 
  grade,
  individual,
  count(*) over (partition by school, grade) as "class size"
from students
QUALIFY (age < 16 and ACT_score is not null)

I rewrote your query in a simpler form, no CTE needed:

SELECT t1.school
    ,t1.grade
    ,t1.individual
    ,t2.cnt AS 'class size'
FROM students t1
LEFT JOIN (
    SELECT school
        ,grade
        ,count(*) AS 'cnt'
    FROM students 
    GROUP BY school, grade
) AS t2 ON t1.school = t2.school
        AND t1.grade = t2.grade
WHERE t1.age < 16
    AND t1.ACT_score IS NOT NULL