I seem to recall an issue where the select statement had a column that was defined by a select statement (like select count(*) from person group by something) and they wanted to group by those counts in the final query, which had something like this:
select a, b, c, select count(*) from X GROUP BY Y as group_counts, count(*) as counts
FROM table
GROUP BY a,b,c, select count(*) from X GROUP BY Y as group_counts
The final GROUP BY couldn’t handle the select statement in the GROUP BY clause and you couldn’t refer to the column as ‘group_counts’ because that column doesn’t actually exist (as we see reported in MSSQL server from the original post)
So, the solution in that case was to break out the group counts into a separate subquery, that you join to the main FROM:
select a, b, c group_counts, count(*) as counts
FROM
(
select a, b, c, select count(*) as group_counts
from table
GROUP BY a, b, c
) t
GROUP BY a,b,c, group_counts
Something like that. My point is that not all DBMS have the same support for GROUP BY (sometimes you can use colum indexes, sometimes you can use complex expressions). By ‘hiding’ things beneath subqueries so that you get a ‘named’ column to group by, I feel like you ‘normalize’ the results into a form that is acceptable by all RDBMS.
Happy for you to prove me wrong tho!
-Chris