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

As mentioned in the comments, you need to put your 3 columns (col1, col2 & col3) into the GROUP BY. Unlike these 3 columns, dbo.our_table is not needed in the GROUP BY as it is a string.

SQL Server executes the components of SELECT queries in a particular order starting with FROM, then WHERE, GROUP BY, etc. In this case, SQL Server doesn't recognize the aliases table_name & table_id in the GROUP BY because they are not set until the SELECT, which is executed after the GROUP BY. Googling "SQL Server SELECT query execution order" should give you a number of resources which will explain the order of execution in more detail.

Answer 2

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;