Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
377 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 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