Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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

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
jazjef
Asked:
jazjef
  • 2
1 Solution
 
AnujSQL Server DBACommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jazjefAuthor Commented:
Thanks anujnb.... that works; looks great.
0
 
Rey Obrero (Capricorn1)Commented:
jazjef,

not sure how you get this expressions working

PO = SUM(Fielding.PO), A = SUM(Fielding.A), E = SUM(Fielding.E)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now