Home » SQL & PL/SQL » SQL & PL/SQL » How to sum values on individual records?
How to sum values on individual records? [message #191128] Mon, 04 September 2006 22:57 Go to next message
bigtoque
Messages: 2
Registered: September 2006
Junior Member
I'm having a hell of a time trying to get this to work and I'm sure it's a relatively simple solution...

I'm managing the stats for my hockey team and in this particular select statement, I'm trying to sort the top point scorers in a single game.

IE, I have 1 player right now who holds positions #1 and #3 for most points in a single game, but my code sums the points from every game he's played in.

SELECT players.PID PID, players.f_Name fName, players.l_Name lName, SUM (player_stats.G + player_stats.A) PTS 
FROM players, player_stats 
WHERE players.PID = player_stats.PID 
AND year = 2005 
AND league = 'Spring' 
GROUP BY players.PID ORDER BY PTS DESC


How can I modify this so that G and A are summed on only single records, and not all the records?
Re: How to sum values on individual records? [message #191129 is a reply to message #191128] Mon, 04 September 2006 23:37 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Send some sample data of both tables and desired output.
Re: How to sum values on individual records? [message #191134 is a reply to message #191128] Tue, 05 September 2006 00:15 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
If the scores have to be added for individual records only, then you don't need the SUM and GROUP BY at all. Does this work for you?

SELECT players.PID PID, players.f_Name fName, players.l_Name lName, (player_stats.G + player_stats.A) PTS 
FROM players, player_stats 
WHERE players.PID = player_stats.PID 
AND year = 2005 
AND league = 'Spring' 
ORDER BY PTS DESC
Re: How to sum values on individual records? [message #191269 is a reply to message #191134] Tue, 05 September 2006 07:46 Go to previous message
bigtoque
Messages: 2
Registered: September 2006
Junior Member
hobbes wrote on Mon, 04 September 2006 23:15

If the scores have to be added for individual records only, then you don't need the SUM and GROUP BY at all. Does this work for you?

SELECT players.PID PID, players.f_Name fName, players.l_Name lName, (player_stats.G + player_stats.A) PTS 
FROM players, player_stats 
WHERE players.PID = player_stats.PID 
AND year = 2005 
AND league = 'Spring' 
ORDER BY PTS DESC



That worked perfectly.

I figured I didn't need the GROUP BY part (because I knew I didn't want to group my data), but I didn't realize I could just use "(player_stats.G and player_stats.A) PTS" without the SUM function Embarassed

Thanks so much Smile
Previous Topic: Marked columns
Next Topic: PL/SQL and System Tablespace
Goto Forum:
  


Current Time: Sat Dec 03 22:01:47 CST 2016

Total time taken to generate the page: 0.10889 seconds