Solved

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

Posted on 2012-04-04
7
316 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
  • 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 119

Expert Comment

by:Rey Obrero
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 96
Query Peformance + mulitple query plans 9 47
SQL Select Statement 2 20
SQL Pivot add row totals 2 0
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now