Solved

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

Posted on 2012-04-04
7
320 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 59
SQL Server 2012 r2 - Make Temp Table Query Faster 5 42
VB.NET 2008 - SQL Timeout 9 24
Sql Query 6 65
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.
In this article I will describe the Copy Database Wizard 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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

770 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