6

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?!

John Smith
  • 145
  • 1
  • 2
  • 5

2 Answers2

9

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;
e_i_pi
  • 4,590
  • 4
  • 27
  • 45
  • Thanks for the suggestion, I had tried something like this before however the problem with your suggestion is that it only adds up the runs on the matches when they finished out. I need the query to add up all the players runs (from every game, irrespective of whether they were out or not) and then divide only by the number of times they were out. For example, if a player had matches as follows: 10 runs (out), 20 runs (out), 30 runs (not out), their average should by (10+20+30)/2 = 30 – John Smith Aug 04 '17 at 10:39
  • What is your expected value if someone plays two innings, scores 20 in each, and is not out in each? – e_i_pi Aug 04 '17 at 12:30
  • good question, something I should have considered, given they technically don't have an average if they haven't ever been out then displaying 'N/A' or something for that particular player would be best – John Smith Aug 04 '17 at 12:55
  • I've updated my answer. In the case that the player has not been dismissed it returns `NULL`, which you can handle during rendering, if you want it to display as N/A. – e_i_pi Aug 04 '17 at 20:39
2

You can use

SUM(CASE WHEN playerout = 'out' THEN 1 ELSE 0 END)
Ferdinand Gaspar
  • 2,043
  • 1
  • 8
  • 17