Solved

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

Posted on 2012-04-04
7
322 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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