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.

Answer

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.