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
348 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 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

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

810 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