Reputation: 2628
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
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