[RESOLVED] query returning NULL

[jonzy]

Trying to get a percentage (ROUND) off of two SUM columns but everything I try does not work. Is this even possible?

The query I am trying:

SELECT players.id, players.fname, players.lname, teams.name, 
player_stats.team_id, divisions.name, 
ROUND( ( 1.00 - ( SUM( player_stats.goalsa ) / SUM( player_stats.shotsa ) ) ) , 3 ) 
AS total_svp

Trying to get SUM of both goalsa and shotsa, divide goalsa by shotsa, subtract that from 1.00, and ROUND that to 2 decimals.

I can do it in PHP no problem, but am hoping to save some time by doing the calculation in the query.

Thanks in advance.

[jonzy]

Update on this, the query is working but apparently where SUM(player_stats.shotsa) turns out to be 0, can't divide by zero, so it is returning NULL on some. Any way to avoid this?

Tried adding
AND total_svp != 'NULL'
but received error, #1054 - Unknown column 'total_svp'

[bogu]

HAVING total_svp != 'null'

[Piranha]

I don't think that null is a string in SQL, making the query a bit harder. Normally you can use IS NOT NULL, but I don't know if it works with HAVING. If not you can do this:

HAVING SUM(player_stats.shotsa) > 0

[jonzy]

Great, thanks!