CODEWITHSUNDEEP
sql-servert-sql
Philip
Philip

Reputation: 2628

XML Path Subquery with Group BY causing Aggregate error

I have the below query and I'm really stuck on how I can get the results returned and avoiding the below error.

Msg 144, Level 15, State 1, Line 40 Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

SELECT Stuff((SELECT ', ' + typename 
              FROM   visittype VD 
                     LEFT JOIN lookup.type D 
                            ON VD.typeid = D.typeid 
              WHERE  visitid = V.visitid 
              ORDER  BY D.typename 
              FOR xml path(''), type).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, 
       N'') 
                AS Value1, 
       Count(*) AS Value2 
FROM   visit V 
WHERE  V.startdate >= '2018-03-31 14:00:00.0000000 +00:00' 
       AND V.enddate <= '2018-06-30 13:59:59.9990000 +00:00' 
GROUP  BY Stuff((SELECT ', ' + typename 
                 FROM   visittype VD 
                        LEFT JOIN lookup.type D 
                               ON VD.typeid = D.typeid 
                 WHERE  visitid = V.visitid 
                 ORDER  BY D.typename 
                 FOR xml path(''), type).value('text()[1]', 'NVARCHAR(MAX)'), 1, 
          1, N'')

Upvotes: 0

Views: 111

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

You could use subquery/cte:

WITH cte AS (
     SELECT Stuff((SELECT ', ' + typename 
              FROM   visittype VD 
              LEFT JOIN lookup.type D 
                ON VD.typeid = D.typeid 
              WHERE  visitid = V.visitid 
              ORDER  BY D.typename 
              FOR xml path(''), type).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, 
       N'') AS Value1
    FROM   visit V 
    WHERE V.startdate >= '2018-03-31 14:00:00.0000000 +00:00' 
      AND V.enddate <= '2018-06-30 13:59:59.9990000 +00:00' 
)
SELECT  Value1, COUNT(*) AS Value2
FROM cte
GROUP BY Value1;

Upvotes: 1

Related Questions