Solved

How do I roll-up/combine multiple records for the same people using SQL so each person only has 1 line of data?

Posted on 2012-04-03
4
340 Views
Last Modified: 2012-04-03
I have an SQL query that return multiple records for the same player. I want to aggregate these records on 3 column fields.

Here's the query:

SELECT Fielding.PlayerID, Master.nameFirst, Master.nameLast, Fielding.yearID, Fielding.teamID, PO = SUM(Fielding.PO), Fielding.A, Fielding.E
FROM Master INNER JOIN Fielding ON Master.playerID = Fielding.playerID
WHERE((Fielding.yearID)=1985) AND (nameLast LIKE '%') AND (Fielding.teamID)='kca'
GROUP BY Fielding.playerID, nameFirst, nameLast, yearID, teamID, PO, A, E


For example:
Buddy Biancalana has 3 rows of data; he has PO values of 0, 3, 80 --- in addtion he has A values of 0, 1, 168 ----- he also has E values of 0, 0, 10

His records look like this:

playerID              nameFirst      nameLast              yearID      teamID      PO      A      E

biancbu01      Buddy            Biancalana      1985      KCA              0      0      0
biancbu01      Buddy            Biancalana      1985      KCA              3      1      0
biancbu01      Buddy            Biancalana      1985      KCA              80      168      10



How do I make it look like this for each player in the recordset returned by the statement?

playerID              nameFirst      nameLast              yearID      teamID      PO      A      E

biancbu01      Buddy            Biancalana      1985      KCA              83      169      10

I just want to roll-up or SUM the PO, A, and E columns for each player.

Thanks.

PS: sorry the columns are a bit out of line; I'll send an xls file to you if you want one.
0
Comment
Question by:jazjef
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 500 total points
ID: 37801316
Remove the aggregate columns from group by

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)=1985) AND (nameLast LIKE '%') AND (Fielding.teamID)='kca'
GROUP BY Fielding.playerID, nameFirst, nameLast, yearID, teamID
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37801391
try this query


SELECT Fielding.PlayerID, Master.nameFirst, Master.nameLast, Fielding.yearID, Fielding.teamID, SUM(Fielding.PO) as [Sum PO], Sum(Fielding.A) as [Sum A], Sum(Fielding.E) as [Sum E]
FROM Master INNER JOIN Fielding ON Master.playerID = Fielding.playerID
WHERE((Fielding.yearID)=1985) AND (nameLast LIKE '%') AND (Fielding.teamID)='kca'
GROUP BY Fielding.playerID, nameFirst, nameLast, yearID, teamID
0
 
LVL 4

Author Closing Comment

by:jazjef
ID: 37802268
Thanks anujnb.... that works; looks great.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37802300
jazjef,

not sure how you get this expressions working

PO = SUM(Fielding.PO), A = SUM(Fielding.A), E = SUM(Fielding.E)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

896 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