Adding rows od data together

Question

Adding rows od data together

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
April 26, 2011 at 9:06 am

#249344

Hi everyone - hope someone can help.
I have a table containing around 1,000 rows. Each row consists of the following 3 fields: Name, Attendances and Did_Not_Attends. I want to be able to merge 2 of the names together ie I would like North and South which currently appear in different rows to be merged into a new named row called North_South with all the accompanying information on Attendances and Did_Not_Attends aggregated in the new row. I have tried using the CASE statement but this just changes the Name field to the new name (North_South) but the rows are still separated and have not been aggregated. How can i merge these rows so that they appear as a new aggregated row?
In other words how do i get the following:
North, 1, 2
North, 2, 3
East, 1, 3
South, 1, 1
West, 2, 2
West, 2, 1
To appear as:
North_South 4, 6
East, 1, 3
West, 2, 2
West, 2, 1
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Your question is not understandable what you are trying to do with this North_south and why not east_west.I am not sure for what you are looking for

declare @Temp table (Name varchar(15), Attendances int,Did_Not_Attends int)

insert into @Temp values

('North', 1, 2),

('North', 2, 3),

('East', 1, 3),

('South', 1, 1),

('West', 2, 2),

('West', 2, 1)

Select 'South_North' as Name, SUM(Attendances) as Attendances ,SUM(Did_Not_Attends) as Did_Not_Attends

from @Temp where Name in('South','North')

UNION ALL

Select * from @Temp where Name not in('South','North')

I have derived this based on the o/p and not on the requirement which you had given here.Dont use this one until for what you are looking for.Be clear with the data since peoples here are to help you based on your requirement.

Thanks
Parthi

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

What are the rules associating different names?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 3

The name fields are actually irrelevant - i just wanted to make a distinction between the names of the various rows. Essentially i need to add two rows together (in my example North and South) and leave the rest of the rows as they are (East and West as stated but equally Blue, Red, Orange, Green, etc)

I think you have given me the correct solution anyway - using the where clause to group the rows together - i'll give it a try.

Thanks for your help, much appreciated!!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

-- Create some sample data

DROP TABLE #Temp

CREATE TABLE #Temp (Name VARCHAR(5), Attendances INT, Did_Not_Attends INT)

INSERT INTO #Temp (Name, Attendances, Did_Not_Attends)

SELECT 'North', 1, 2 UNION ALL

SELECT 'North', 2, 3 UNION ALL

SELECT 'East', 1, 3 UNION ALL

SELECT 'South', 1, 1 UNION ALL

SELECT 'West', 2, 2 UNION ALL

SELECT 'West', 2, 1

-- Create a table of associations

DROP TABLE #Links

CREATE TABLE #Links (Name1 VARCHAR(5), Name2 VARCHAR(5))

INSERT INTO #Links (Name1, Name2)

SELECT 'North', 'South'

-- Solution

SELECT

Name = l.Name1 + '_' + l.Name2,

Attendances = SUM(Attendances),

Did_Not_Attends = SUM(Did_Not_Attends)

FROM #Temp t

INNER JOIN #Links l ON l.Name1 = t.Name OR l.Name2 = t.Name

GROUP BY l.Name1 + '_' + l.Name2

UNION ALL

SELECT

Name,

Attendances,

Did_Not_Attends

FROM #Temp t

WHERE NOT EXISTS (SELECT 1 FROM #Links l WHERE l.Name1 = t.Name OR l.Name2 = t.Name)

Results:

Name Attendances Did_Not_Attends

North_South 4 6

East 1 3

West 2 2

West 2 1

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 5

Cheers Chris

It looks like i will live to fight another day!

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 6

Hi Chris

This solution worked brilliantly - thanks again for your help.

How would this work if i wanted to merge a new set of rows to the same table?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

Extend the links table, which would be better designed normalised like this:

GroupName Name

North_South North

North_South South

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 8

ByronOne

SSCarpal Tunnel

Points: 4656

May 27, 2011 at 5:19 am

#1330895

Cheers Chris - worked a treat.