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

[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
LVL 4
jazjefAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick TallaricoDecision Support and Systems AnalystCommented:
take the group by statement and remove the PO, A, E fields.
See if that works
Rey Obrero (Capricorn1)Commented:
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
jazjefAuthor Commented:
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....
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Patrick TallaricoDecision Support and Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jazjefAuthor Commented:
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.
Patrick TallaricoDecision Support and Systems AnalystCommented:
Sure, no problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.