In SQL Server, how to concat --> group by using the concat column

Question

SELECT
    'dbo.our_table' as table_name,
    CONCAT(col1, '-', col2, '-', col3) as table_id,
    COUNT(*) as ct 
FROM dbo.our_table
group by table_name, table_id
-- group by 1, 2 -- this doesn't work either...
order by ct desc

This does not work in SQL server because it does not recognize table_name or table_id. I understand that this can be done by nesting a 2nd SELECT clause into the FROM, so that table_name and table_id are explicitly available, however I am trying to understand if it is possible to achieve this output without having to create a nested SELECT statement, but rather by keeping the structure of my current query but only making a tweak.

Answer 1

You need to specify the full calculation for the GROUP BY as well as for the SELECT. This is because GROUP BY is logically considered before SELECT, so cannot access those calculations.

You could do it like this (table_name is not necessary because it's purely computed):

SELECT
    'dbo.our_table' as table_name,
    CONCAT(col1, '-', col2, '-', col3) as table_id,
    COUNT(*) as ct 
FROM dbo.our_table
group by CONCAT(col1, '-', col2, '-', col3)
order by ct desc;

But much better is to place calculations in a CROSS APPLY, this means it is accessible later by name as you wished:

SELECT
    'dbo.our_table' as table_name,
    v.table_id,
    COUNT(*) as ct 
FROM dbo.our_table
CROSS APPLY (VALUES (CONCAT(col1, '-', col2, '-', col3) ) ) as v(table_id)
group by v.table_id
order by ct desc;