Concatenating rows using group by and aggregate functions

Question

Imagine we have a table such as:

Number Value GroupValue
1      FOO    GR1
22     BAR    GR2
100    FOO3   GR1
20     BAR23  GR2

I want to get the sum values of these rows based on group value,but also ,i want to see which rows it has grouped by in another column such as:

SUM  values groups
101  FOO,FOO3 GR1
42   BAR,BAR23 GR2

How can i achieve that in sql?

Tried:

> SELECT  SUM(ID) AS SUM ,
        STUFF(( SELECT  ',' + A
                FROM    dbo.Table_1 AS T2
                WHERE   A = T2.A
              FOR
                XML PATH('')
              ), 1, 1, '') AS A ,
        B
FROM    dbo.Table_1 AS T
GROUP BY B ,
        A

But this can not handle the groups properly...

Hart CO · Accepted Answer · 2013-09-18 20:18:20Z

You can't use an alias created in the SELECT list in your GROUP BY. You can fix this by separating your listing from your aggregation (cte/subquery):

WITH cte AS (SELECT  *
                     ,STUFF(( SELECT  ',' + Value
                              FROM    Table1 AS T2
                              WHERE   T.GroupValue = T2.GroupValue
                              FOR XML PATH('')
                            ), 1, 1, '') AS Value_List
              FROM    Table1 AS T)
SELECT  SUM(Number) AS Total
       ,Value_List
       ,GroupValue
FROM cte
GROUP BY Value_List, GroupValue

Demo: SQL Fiddle

Or you can use a partitioned SUM() (window function):

SELECT  DISTINCT SUM(Number) OVER(PARTITION BY GroupValue) AS Total
        ,STUFF(( SELECT  ',' + Value
                 FROM    Table1 AS T2
                 WHERE   T.GroupValue = T2.GroupValue
                 FOR XML PATH('')
               ), 1, 1, '') AS Value_List
        ,GroupValue
FROM    Table1 AS T

Demo: SQL Fiddle

Steve · Accepted Answer · 2013-09-18 19:53:01Z

0

You just need to make a small change, stick your SQL without the group into another select with the group. I think I converted your SQL right:

SELECT SUM(ID) as IdSum, B FROM (
SELECT  ID,
        STUFF(( SELECT  ',' + A
                FROM    dbo.Table_1 AS T2
                WHERE   A = T2.A
              FOR
                XML PATH('')
              ), 1, 1, '') AS B
FROM    dbo.Table_1 AS T) AS B2
GROUP BY B

answered Sep 18, 2013 at 19:53

Steve

5,5553 gold badges20 silver badges32 bronze badges

1 Comment

Steve Over a year ago

The problem is trying to group on a "Calculated" field. The nested select allows you group on the result. This can be done with APPLY as well but this example is easier for most people to understand.

Ross Bush · Accepted Answer · 2013-09-18 19:54:04Z

0

You could nest the list one level deeper

...

SELECT
    IDList1=    
        SUBSTRING((
            SELECT  ', ' + CAST(TargetID AS NVARCHAR(10))
            FROM 
            (
                SELECT 
                   DISTINCT TargetID 
                FROM 
                   TargetTable 
                WHERE 
                  TargetIDTableField2=MAINQUERYALIAS.TargetIDTableField2
            )
            AS UP2
            ORDER BY 
             TargetID FOR XML PATH( '' )
        ), 3, 1000 )
FROM
(
   ...
) AS MAINQUERYALIAS

answered Sep 18, 2013 at 19:54

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Comments

Angelo · Accepted Answer · 2013-09-19 02:28:19Z

0

download this and run this script http://groupconcat.codeplex.com/releases/view/106409 and you can solve your problem as simple as this

select SUM(Number), dbo.GROUP_CONCAT(Value)
from Table_2 
group by GroupValue

please mark as answered if this answer ur question thanks

answered Sep 19, 2013 at 2:28

Angelo

3354 silver badges10 bronze badges

Collectives™ on Stack Overflow

Concatenating rows using group by and aggregate functions

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related