Solved

How do I combine duplicate records with the same ID in an SQL statement?

Posted on 2012-04-04
7
323 Views
Last Modified: 2012-04-04
[I'm using SQL Server Express 2008; Management Studio]

*NOTE:
Because the below graphs will be messed up on EE, an XLS spreadsheet is attached with SQL statement, the data the statement returns, and detailed explanation.

Example:
For Player biancbu01 (Buddy Biancalana) as seen in rows 3 and 4 (BEFORE), I would like to have only 1 record returned that combines his PO, A, and E values (AFTER).

BEFORE:

PlayerID      nameFirst      nameLast      yearID   teamID           PO      A      E      

balbost01      Steve      Balboni      1985           KCA           1573      101      12      
beckwjo01      Joe            Beckwith      1985           KCA           7              12      2      
biancbu01      Buddy      Biancalana      1985           KCA           3              1      0      
biancbu01      Buddy      Biancalana      1985           KCA           80      168      10      
blackbu02      Bud            Black            1985           KCA           6              30      4      
brettge01      George      Brett            1985      KCA           107      339      15      


AFTER:

PlayerID      nameFirst      nameLast      yearID     teamID      PO         A            E      

balbost01      Steve      Balboni      1985             KCA              1573         101      12      
beckwjo01      Joe            Beckwith      1985             KCA              7         12            2      
biancbu01      Buddy      Biancalana      1985             KCA              83         169      10      
blackbu02      Bud            Black            1985             KCA              6         30            4      
brettge01      George      Brett            1985             KCA              107         339      15
EE-Roll-up-PO--A--E-values.xls
0
Comment
Question by:jazjef
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37809085
take the group by statement and remove the PO, A, E fields.
See if that works
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37809089
create a totals query, use SUM in fields PO,A,E and Group BY in fields
PlayerID, nameFirst, nameLast, yearID, teamID

like this

select PlayerID, nameFirst, nameLast, yearID, teamID,sum(PO),sum(A),sum(E)
from tableName
group by PlayerID, nameFirst, nameLast, yearID, teamID
0
 
LVL 4

Author Comment

by:jazjef
ID: 37809131
Yes... that works great stpmt11!.  But now there's a new problem..... when I try to do a calculation after combining records I get an ERR MSG. Here's my statement where I've added the calculation of FPCT:

SELECT Fielding.PlayerID, Master.nameFirst, Master.nameLast, Fielding.yearID,
Fielding.teamID, PO = SUM(Fielding.PO), A = SUM(Fielding.A), E = SUM(Fielding.E),
FPCT = ROUND(((SELECT CAST(PO AS float) + CAST(A AS float)) / (CAST(PO AS float) + CAST(A AS float) + CAST(E AS float))),3)
FROM Master INNER JOIN Fielding ON Master.playerID = Fielding.playerID
WHERE((Fielding.yearID)=1951) AND (nameLast LIKE '%') AND (Fielding.teamID)='bro' AND PO > 0
GROUP BY Fielding.playerID, nameFirst, nameLast, yearID, teamID, FPCT

I have a Catch-22 situation now:
The ERR message says I need to have PO and A in the GROUP BY clause to do the calculation.

But.....if I do the calculation, then the records are no longer aggregated because I have included PO, A, E in the GROUP BY clause.

I can have one or the other, but not both....
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Accepted Solution

by:
Patrick Tallarico earned 500 total points
ID: 37809169
Try to force the sum functions to run first, then you should be able to do the calculation on the summed fields.  I don't have my test environment available, so I can't check the syntax, but it should be close to the following.

select *,FPCT = ROUND(((CAST(PO AS float) + CAST(A AS float)) / (CAST(PO AS float) + CAST(A AS float) + CAST(E AS float))),3)
from(
SELECT Fielding.PlayerID, Master.nameFirst, Master.nameLast, Fielding.yearID,
Fielding.teamID, PO = SUM(Fielding.PO), A = SUM(Fielding.A), E = SUM(Fielding.E),
FROM Master INNER JOIN Fielding ON Master.playerID = Fielding.playerID
WHERE((Fielding.yearID)=1951) AND (nameLast LIKE '%') AND (Fielding.teamID)='bro' AND PO > 0
GROUP BY Fielding.playerID, nameFirst, nameLast, yearID, teamID) as t1
0
 
LVL 4

Author Comment

by:jazjef
ID: 37809190
Unbelievable. You nailed it perfectly----I had to remove one comma and BANG, perfect result. That's SQL-egant stpmt11. And I'll remember to try to move certain arguments to the front and complete them first if I have a similar problem again... THANKS.
0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37809195
Sure, no problem.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question