The row is grouped by function with other aggregate functions.

Question

Is it possible to combine lines with one or more other groups by function, for example, sum, avg, count, etc.

Let's say I have the following table

Id Name Order Value 1 a 1 100 2 b 2 200 3 c 1 300 4 d 1 100 5 e 2 300 

Now, if I want the result to be something like this

 Order Name Value Count 1 a,c,d 500 3 2 b,e 500 2 

How can I achieve the same using a query on a SQL server.

Answer 1

Table example

 create table t123 (Id int, Name varchar(10), [Order] int, Value int) insert t123 select 1,'a','1',100 union all select 2,'b','2',200 union all select 3,'c','1',300 union all select 4,'d','1',100 union all select 5,'e','2',300 

Query for SQL Server 2005 and later

 select a.[order], STUFF(( select ','+b.name from t123 b where b.[order] = a.[order] order by b.name for xml path('a'), type).value('.','nvarchar(max)'),1,1,'') Name, SUM(a.value) value, COUNT(*) [count] from t123 a group by a.[order] 

Exit

 order Name value count ----------- ------------ ----------- ----------- 1 a,c,d 500 3 2 b,e 500 2 

Answer 2

Try using this.

• I posted your original request in the CTE.
• Then I selected it and grouped by order.
• Then I intersect with a subquery that gets a set of comma-separated names for each order in the outer query.
• Since I also selected the name column, I also had to group the name column.

Like this:

 ;WITH cte(id, n, o, v) as ( SELECT Id, Name, Order, Value FROM .... ) SELECT o, names, SUM(v), COUNT(*) FROM cte AS outer CROSS APPLY ( SELECT Name+',' FROM cte AS inner WHERE outer.o = inner.o ORDER BY Name FOR XML PATH('') ) n(names) group by o, names 

Answer 3

If you are using MS SQL Server 2005 or later, you can create custom aggregate functions.

MSDN: CREATE AGGREGATE (Transact-SQL)

MSDN: invoke custom CLR aggregation functions