CODEWITHSUNDEEP
sqlsqlite
igigi
igigi

Reputation: 45

how to add WHERE clause on SUM? (SELECT JOIN SUM ORDER BY GROUP BY WHERE)

SELECT player.name,player.handicap, 
SUM(tournament_player.points) as total_points, 
COUNT(tournament_player.player_id) as attendances
FROM player
INNER JOIN tournament_player ON player.id=tournament_player.player_id
GROUP BY player.id
ORDER BY SUM(tournament_player.points) DESC

The above works perfectly, but it also selects players with no points at all. i.e where their total of points is equal to 0

How to add a WHERE clause like that 

WHERE SUM(tournament_player.points) > 0

Regardless where I put the clause it always gives me an error near by the WHERE clause :(

SELECT player.name,player.handicap, 
SUM(tournament_player.points) as total_points, 
COUNT(tournament_player.player_id) as attendances
FROM player
INNER JOIN tournament_player ON player.id=tournament_player.player_id
GROUP BY player.id
WHERE  SUM(tournament_player.points) > 0
ORDER BY SUM(tournament_player.points) DESC

-- UPDATE --

Thank you both! Wasn't aware of HAVING clause. Working statement:

SELECT player.name,player.handicap, 
SUM(tournament_player.points) as total_points, 
COUNT(tournament_player.player_id) as attendances
FROM player
INNER JOIN tournament_player ON player.id=tournament_player.player_id
GROUP BY player.id
HAVING  SUM(tournament_player.points) > 0
ORDER BY SUM(tournament_player.points) DESC

Upvotes: 0

Views: 54

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

You need to use HAVING when working with Aggregated columns. 

SELECT player.name,player.handicap, 
SUM(tournament_player.points) as total_points, 
COUNT(tournament_player.player_id) as attendances
FROM player
INNER JOIN tournament_player ON player.id=tournament_player.player_id
GROUP BY player.id
HAVING SUM(tournament_player.points) > 0
ORDER BY SUM(tournament_player.points) DESC

Upvotes: 2

Related Questions