Aggregate with row merge

Question

I have a table my_table that has columns state, month, ID, and sales.

My goal is to merge different rows that have the same state, month, ID into one row while summing the sales column of these selected rows into the merged row.

For example:

state    month    ID    sales
-------------------------------
FL       June     0001   12,000
FL       June     0001    6,000
FL       June     0001    3,000
FL       July     0001    6,000
FL       July     0001    4,000
TX       January  0050    1,000
MI       April    0032    5,000
MI       April    0032    8,000
CA       April    0032    2,000

This what I am supposed to get

state    month    ID    sales
-------------------------------
FL       June     0001   21,000
FL       July     0001   10,000  
TX       January  0050   1,000
CA,MI    April    0032   15,000

TX and MI are coming together ? what is the logic behind it ? — Ashutosh Arya, Sep 20 '13 at 06:05
@Ashutosh Arya He wants it grouped by month sales, for every state altogether. — mavrosxristoforos, Sep 20 '13 at 06:24

Himanshu · Accepted Answer · 2013-09-20T06:36:31.617

2

You need to use GROUP_CONCAT for that:

SELECT GROUP_CONCAT(DISTINCT State) AS State
 , Month, ID, SUM(Sales)
FROM Table1
GROUP BY Month, ID;

See this SQLFiddle

Update (For SQL Server)

For SQL Server you can use STUFF() for that:

SELECT  state = 
    STUFF((SELECT DISTINCT ' , ' + state
           FROM Table1 b 
           WHERE b.ID = a.ID
             AND b.month = a.month
          FOR XML PATH('')), 1, 2, '')
    ,month, ID, SUM(sales) AS Sales
FROM Table1 a
GROUP BY month,ID;

See this SQLFiddle

edited Sep 20 '13 at 06:36

answered Sep 20 '13 at 06:24

Himanshu

31,810
31
111
133

What is equivalent "GROUP_CONCAT" In SQL Server? – Sridhar Sep 20 '13 at 06:31
@srid99: i was checking the same, there is no direct solution of that, the answer i posted will work in you case, but you can also refer http://stackoverflow.com/questions/8948531/group-concat-in-sql-server-2008 – Ashutosh Arya Sep 20 '13 at 06:33
1

@all : Was working on stuff.... actually this is dummy query, actual query is little bit more complex... btw thanks – Sridhar Sep 20 '13 at 06:44

score 1 · Answer 2 · edited Sep 20 '13 at 06:40

1

Please try this:

SELECT B.STATE, A.ID, SUM(A.SALES) AS SALES 
FROM MYTABLE A
INNER JOIN
  (SELECT DISTINCT 
   CASE WHEN A.state = B.STATE THEN A.STATE ELSE A.STATE+','+B.STATE END AS STATE,
   ID
   FROM MYTABLE A
   INNER JOIN MYTABLE B ON A.ID = B.ID) B
ON A.STATE = B.STATE
GROUP BY B.state, A.month, A.ID

Let me know in case it query need any rectification.

edited Sep 20 '13 at 06:40

user272735

10,473
9
65
96

answered Sep 20 '13 at 06:26

Ashutosh Arya

1,138
2
8
14

score 0 · Answer 3 · answered Sep 20 '13 at 06:02

0

what you actually want to do is called grouping and not merging rows. grouping several rows with common values while aggregating different values.

use Group By clause to achieve the above:

select state,month,ID sum(sales)
from my_table
group by
state, month, ID

to read more about group by see here : http://www.w3schools.com/sql/sql_groupby.asp

answered Sep 20 '13 at 06:02

Mortalus

10,574
11
67
117

I think you should group by month, and try to concatenate the states, because he is not asking for a sum based on states, but a sum based on months, for the existing states of each month. – mavrosxristoforos Sep 20 '13 at 06:23
I want to actually take sum of sales without grouping on state but while displaying want to merge different state into single row. - thanks – Sridhar Sep 20 '13 at 06:25

Aggregate with row merge

3 Answers3

See this SQLFiddle

Update (For SQL Server)

See this SQLFiddle