SUM &amp; GROUP BY problem

SUM & GROUP BY problem

predator

I have this rather large Query in which I select Players (hockey players), their team, and their stats (GP, G, A, TP)
during a certain season.

The thing is that during a season a player could play for
more than one team, which means that two seperate
rows could be returned: one with the first team he
played for and one row with the second team he played for.

One thing that makes this a tad more difficult is that the
STATS are all in VARCHAR, so I must lpad them to get
them ordered in the correct way.

Anyhow, here is my QUERY:

$rookie = "SELECT player.PlayerID, player.Firstname, player.Lastname, team.TeamID, 
team.Team, stats.PlayerID, stats.KID, MAX(stats.TeamID), stats.GP, stats.G, stats.A, stats.TP

LPAD(stats.GP,2,'0') as GP, LPAD(stats.G,2,'0') as G, LPAD(stats.A,2,'0') as A, (stats.TP,3,'0') as TP,    

SUM(GP), SUM(G), SUM(A), SUM(TP) 

FROM player INNER JOIN stats ON stats.TeamID=team.TeamID 
INNER JOIN team ON player.PlayerID=stats.PlayerID 

WHERE stats.League='NHL' AND stats.EndDate='$Year' AND player.NationID='1' AND player.POS <> 'G' 

GROUP BY stats.PlayerID 
ORDER BY TP DESC, G DESC, GP ASC";

So what is wrong with it? Well, it only returns one player (so the GROUP BY works) but it doesn't SUM the values.

-------EXAMPLE------
If you want it visualised here's how the page currently looks (without GROUP BY and SUM)
http://www.eliteprospects.com/scoring6.php?sort=forwards&Year=1996

Look for player "Markus Näslund" and you'll see that he is listed at #6 as well as #15. I want those two to be added together and listed as one.

LordShryku

And how would it sum them? Using lpad doesn't make them numbers....

ultraslacker

If the query has a GROUP BY clause then it should only have aggregates or grouped attributes. Any real dbms would balk at your query.

SQL has the aggregates COUNT, SUM, MIN, MAX, and AVG.

predator

ultraslacker,
I didn't follow you quite there. Perhaps it's my mediocre English or this is just too complicated for me 🙂 Probably a combination I'd guess.

Is it impossible to do this SELECT the way I want it?
or can I somehow re-write it and get it to work?

thanks

Sxooter

OK. Let's suppose we have a table like this:

i1 i2

0 0
0 1
0 2
1 1
1 3
1 4

and we run this:

select i1,i2 from table group by i1;

what should our output be for i2? If we group by i1, we have this dataset:

0 [0,1,2]
1 [1,3,4]

which number for i2 do we pick? Without an aggregate function to operate on i2, the database doesn't know what you want. Proper behaviour would be to throw an error telling you that. So, we re-write it to:

select i1,max(i2) from table group by i1;

and get

0,2
1,4

Or you could use min(i2) and get

0,0
1,1

This tendency of MySQL to just toss out whatever row it finds can lead to some rather odd and unreproduceable results. So, if it isn't grouped by, it needs to have an aggregate run across it to be reliable and correct.