Aggregate column values into a list produces an error

The following query

DECLARE @SNH TABLE
             (
                 dt date, 
                 QueueName varchar(10),
                 SN varchar(10)
             )

INSERT INTO @SNH (Dt, QueueName, SN)
VALUES ('2001-04-04', 'Queue01', 'Q01SN01'),
       ('2001-04-05', 'Queue01', 'Q01SN01'),
       ('2001-04-06', 'Queue01', 'Q01SN01'),
       ('2001-04-04', 'Queue02', 'Q02SN01'),
       ('2001-04-05', 'Queue02', 'Q02SN01'),
       ('2001-04-06', 'Queue02', 'Q02SN02')

declare @QH table (
DT date, 
QueueName varchar(10)
)  
insert into @QH(DT, QueueName)
values
('2001-04-04','Queue01'),
('2001-04-05','Queue01'),
('2001-04-06','Queue01'),
('2001-04-04','Queue02'),
('2001-04-05','Queue02'),
('2001-04-06','Queue02')

SELECT Distinct
        q.QueueName clnQueueName,
        MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
        MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
        s.SN
FROM @QH q LEFT JOIN @SNH s
ON s.QueueName = q.QueueName 

gives me the table

which I’m aiming to aggregate into a comma separated list with

SELECT Distinct
        q.QueueName clnQueueName,
        MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
        MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
        STRING_AGG(s.SN,',')
FROM @QH q LEFT JOIN @SNH s
ON s.QueueName = q.QueueName AND s.Dt = q.Dt

as follows

Instead

I get: "Msg 8120, Level 16, State 1, Line 36
Column ‘@QH.QueueName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Sorry, I can’t get it.

‘@QH.QueueName’ isn’t even mentioned in the SELECT, only q.QueueName.

What I am missing here?

>Solution :

As per the docs, a group by clause is required if the string_agg is not the only item being selected. Once grouping correctly you no longer need window functions or distinct.

WITH cte AS (
    SELECT
        q.QueueName clnQueueName
        , MIN(q.Dt) clnStartDate
        , MAX(q.Dt) clnEndDate
        , s.SN
    FROM @QH q
    LEFT JOIN @SNH s
        ON s.QueueName = q.QueueName AND s.Dt = q.Dt
    GROUP BY q.QueueName, s.SN
)
SELECT clnQueueName, clnStartDate, clnEndDate
    , STRING_AGG(s.SN,',') SN
FROM cte
GROUP BY clnQueueName, clnStartDate, clnEndDate;