CODEWITHSUNDEEP
sql
Tug Strongly
Tug Strongly

Reputation: 187

Combine similar column data from two rows into one row

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

Upvotes: 3

Views: 4768

Answers (2)

Kermit
Kermit

Reputation: 34055

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

Upvotes: 1

Tim Lehner
Tim Lehner

Reputation: 15251

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.

Upvotes: 4

Related Questions