Apply limit to CASE statement

I am trying to collect counts of different columns for players (you don’t need to know the ins and outs of all the columns). We have a player_1 and player_2 column so I have made it distinct to get total counts, as you can see below:

SELECT player,                  COUNT(*) AS since_start_matches,                  COUNT(CASE WHEN ht_total_goals=0 THEN 1 END) AS since_start_ht_0,                                  COUNT(CASE WHEN la_date BETWEEN CURDATE() - INTERVAL 13 DAY AND CURDATE() THEN 1 END) AS last_14_d_matches,                 COUNT(CASE WHEN ht_total_goals=0 AND la_date BETWEEN CURDATE() - INTERVAL 13 DAY AND CURDATE() THEN 1 END) AS last_14d_ht_0,                 FROM ((SELECT player_1 as player, la_date, ht_total_goals        FROM results ORDER BY la_date DESC, session_start DESC       ) UNION ALL       (SELECT player_2 as player, la_date, ht_total_goals        FROM results ORDER BY la_date DESC, session_start DESC       )      ) p GROUP BY player 

I want to also get the frequency of goals for last 32 games for those players as well.. There isn’t a way to add a LIMIT clause to the CASE WHEN I’m guessing?

E.g, I want to add a column:

COUNT(CASE WHEN ht_total_goals=0 THEN 1 END LIMIT 32 ) AS last_32_games_ht_0 

This won’t work, I’m guessing because the table we are selecting from contains all rows and the CASE WHEN statement won’t allow the limits to apply?

I could do this in a separate query, but ideally I want it all in one.

Help appreciated.

Add Comment
1 Answer(s)

I want to also get the frequency of goals for last 32 games for those players as well.

You will need an additional subquery and then conditional aggregation:

SELECT . . . ,        COUNT(*) AS since_start_matches,         SUM( ht_total_goals = 0 ) AS since_start_ht_0,                     SUM( la_date BETWEEN CURDATE() - INTERVAL 13 DAY AND CURDATE() ) AS last_14_d_matches,        SUM( ht_total_goals = 0 AND la_date BETWEEN CURDATE() - INTERVAL 13 DAY AND CURDATE() ) AS last_14d_ht_0,        SUM( (seqnum <= 32) AND (ht_total_goals = 0) )  FROM (SELECT p.*, ROW_NUMBER() OVER (PARTITION BY player ORDER BY la_date DESC) as seqnum       FROM ((SELECT player_1 as player, la_date, ht_total_goals              FROM results             ) UNION ALL             (SELECT player_2 as player, la_date, ht_total_goals              FROM results             )            ) p      ) p GROUP BY player; 

Notes:

  • ORDER BY in a derived table subquery is really a no-op. It is superfluous, so I removed it.
  • You can simplify your COUNT() columns just by adding the boolean value. This is a very convenient MySQL extension.
Answered on July 16, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.