Menu
I have the following query:
SELECT
Account,
Unit,
SUM(state_fee),
Code
FROM tblMta
WHERE MTA.Id = '123'
GROUP BY Account,Unit
This of course throws an exception because the Code is not in the group by clause. Each state_fee has a code. How do I get this code to display in 1 record (1 code per state_fee which is multiple state_fee per unit) as a comma-separated list? I looked into different solutions on here but I couldn't find any that worked with a group by.
921
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
GROUP BY Clause is utilized with the SELECT statement. GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc. GROUP BY returns only one result per group of data. GROUP BY Clause always follows the WHERE Clause.
You want to use FOR XML PATH construct:
SELECT ACCOUNT,
unit,
SUM(state_fee),
Stuff((SELECT ', ' + code
FROM tblmta t2
WHERE t2.ACCOUNT = t1.ACCOUNT
AND t2.unit = t1.unit
AND t2.id = '123'
FOR XML PATH('')), 1, 2, '') [Codes]
FROM tblmta t1
WHERE t1.id = '123'
GROUP BY ACCOUNT,
unit
See other examples here:
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
