MSSQL Server Why group by alias doesn't work?

August 30, 2022

I’m trying to solve the question in leetcode 1699. Number of Calls Between Two Persons. My solution doesn’t work and it gives an error saying [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'person1'. (207) (SQLExecDirectW) How can I fix it?

SELECT 
    CASE 
        WHEN from_id > to_id THEN to_id
        ELSE from_id
    END AS person1,
    CASE 
        WHEN from_id > to_id THEN from_id
        ELSE to_id
    END AS person2,
    COUNT(duration) AS call_count,
    SUM(duration) AS total_duration
FROM Calls
GROUP BY person1, person2

>Solution :

Sub-queries are used to allow you to define ‘scope’…

SELECT
  person1,
  person2,
  COUNT(duration) AS call_count,
  SUM(duration) AS total_duration
FROM
(
  SELECT 
    CASE 
        WHEN from_id > to_id THEN to_id
        ELSE from_id
    END AS person1,
    CASE 
        WHEN from_id > to_id THEN from_id
        ELSE to_id
    END AS person2,
    duration
  FROM
    Calls
)
  C
GROUP BY
  person1,
  person2

An alternative is to use APPLY

SELECT 
  P.person1,
  P.person2,
  COUNT(C.duration) AS call_count,
  SUM(C.duration) AS total_duration
FROM
  CALLS AS C
CROSS APPLY
(
  SELECT
    CASE 
        WHEN C.from_id > C.to_id THEN C.to_id
        ELSE C.from_id
    END AS person1,
    CASE 
        WHEN C.from_id > C.to_id THEN C.from_id
        ELSE C.to_id
    END AS person2
)
  AS P
GROUP BY
  P.person1,
  P.person2