Combine similar column data from two rows into one row

Question

I have query results with the following characteristics:

LIDCode                         Total   Domain  Region  VSE   Version       
AB02                               15   GLOBAL  CANLA    0     6943 
AB02 - 5925 CENTRE STREET SW       31   GLOBAL  CANLA   30     6943

I am taking data from 2 different tables, but I want to use the longer entry for the LIDCode value. This is one set of results of many. How can I combine these two rows together to get the total of the combined rows? The LIDcode value changes, so I can't use AB02 as a constant value to compare.

The result I need is:

AB02 - 5925 CENTRE STREET SW       46   GLOBAL  CANLA   30     6943

Please show the query you are using right now (as well as the table definition and RDBMS flavor). — BellevueBob, Jan 02 '13 at 22:42

score 4 · Answer 1 · answered Jan 02 '13 at 22:45

4

You might try something like this (as an example in SQL Server):

select max(LIDCode) as LIDCode
      , sum(Total) as Total
      , Domain
      , Region
      , sum(VSE) as VSE -- or max(VSE)
      , Version
from Mytable
group by left(LIDCode, 4), Domain, Region, Version

You may have to group by a different substring of LIDCode (first substring before space, etc.)

Here is a SQL Fiddle to mess with and possibly try in another RDBMS.

answered Jan 02 '13 at 22:45

Tim Lehner

14,813
4
59
76

This ended up working. I had to tweak it a little to hadle some wierd named LIDCodes. Thank you very much for the help in this matter. – Tug Strongly Jan 11 '13 at 18:44
@user1944143 glad to hear! Please upvote/mark as correct so future visitors know this. – Tim Lehner Jan 11 '13 at 18:54

score 1 · Answer 2 · answered Jan 02 '13 at 22:47

1

You can try something like this for MySQL:

SELECT LEFT(LIDCODE, 4) AS LIDCODE, SUM(TOTAL) AS total, `Domain`, `Region`, SUM(VSE) AS vse, `Version`
FROM tbl
GROUP BY LEFT(LIDCODE, 4), `Domain`, `Region`, `Version`

SQL Fiddle

answered Jan 02 '13 at 22:47

Kermit

33,827
13
85
121

Combine similar column data from two rows into one row

2 Answers2

SQL Fiddle

Linked