MSSQL Server Why group by alias doesn't work?

Question

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

Answer 1

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

Answer 2

From_Id and to id need to be the group by. I would bring in your case logic and duration into one query and then apply your aggregate to the outer part.

SELECT
    X.Person1
    , X.Person2
    ,COUNT(X.duration) AS call_count
    ,SUM(X.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
) AS X

GROUP BY
    X.Person1
    , X.Person2