PostgreSQL using CASE WHEN in a select query

Question

I have a simple table in a cricket database that records the 'runs' each player scores in a game. The fields are: the player's name, the runs they scored in a particular game, whether they were 'out' or 'not out' at the end of the match. Obviously players appear as many times in the table as matches they have played

I want to run a query that gives me the average 'runs' per player. In cricket, this is done by taking their total runs and dividing only by the number of times they were 'out', the code I have been trying is something like this:

SELECT name,
       SUM(runs)/COUNT(CASE WHEN playerout = 'out' THEN name END)
FROM players
GROUP BY name;

The idea of this being that for each player, it sums their runs and divides only by the number of times they were 'out', ignoring the 'notout's. This code doesn't work as it still includes the times they were 'notout' I have tried adding 'ELSE 0' in but an error is returned due to integer and character data types being mixed.

Update

The query needs to add up a player's runs in every game, irrespective of whether they finished out or not out and then divide by the number of times they were out. For example if one player had matches as follows: 1. 10 runs, out 2. 20 runs, out 3. 30 runs, not out Their average would by their total runs (10+20+30) = 60, divided by the number of times they were out (2), so their average would be 30.

There is probably an obvious solution which I haven't realised - any suggestions?!

Answer 1

If you want to report on all players irrespective of whether they have been out or not in any match, then you need to define a value for when they have never been out. For the example below, I have used NULL. Doing it this way ensures you don't get a division by zero error.

SELECT
    name,
    CASE WHEN SUM(CASE WHEN playerout = 'out' THEN 1 ELSE 0 END) = 0
        THEN NULL
        ELSE
            SUM(runs)
            /SUM(CASE WHEN playerout = 'out' THEN 1 ELSE 0 END)
    END AS runs_divided_by_dismissals
FROM players
GROUP BY name;