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
364 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
[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
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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