Batters Who Were Out for a Duck This Season — SQL Problem
A duck is an innings in which the batter was dismissed without scoring. A batter who ends on 0 not out has not made a duck.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #455
Problem statement
A duck is an innings in which the batter was dismissed without scoring. A batter who ends on 0 not out has not made a duck.
Return each batter who made at least one duck, with the columns batter and ducks (how many innings they were out for 0). Order the rows by ducks descending, then by batter alphabetically.
Tables
Table: Scorecard
| Column | Type |
|---|---|
| innings_id | int |
| match_id | int |
| batter | varchar |
| runs | int |
| balls_faced | int |
| dismissed | bool |
Primary key: innings_id.
One row per batter per innings. dismissed is 1 if the batter was out and 0 if not out.
Examples
Example 1
Scorecard
| innings_id | match_id | batter | runs | balls_faced | dismissed |
|---|---|---|---|---|---|
| 1 | 301 | Rohan Iyer | 0 | 1 | 1 |
| 2 | 301 | Kabir Khan | 0 | 2 | 0 |
| 3 | 302 | Rohan Iyer | 34 | 22 | 1 |
| 4 | 302 | Arjun Nair | 0 | 4 | 1 |
| 5 | 303 | Rohan Iyer | 0 | 3 | 1 |
| 6 | 303 | Kabir Khan | 0 | 1 | 1 |
| 7 | 303 | Dev Mehta | 51 | 30 | 0 |
Output
| batter | ducks |
|---|---|
| Rohan Iyer | 2 |
| Arjun Nair | 1 |
| Kabir Khan | 1 |
How to solve Batters Who Were Out for a Duck This Season
The definition has two parts — runs = 0 and dismissed = 1 — and the not-out zero is the trap: Kabir's 0 not out in match 301 is not a duck, his 0 in match 303 is.
The direct plan filters the ducks in WHERE and then groups by batter and counts. Because the filter runs before grouping, a batter with no ducks has no rows left and never appears, which is exactly the rule asked for.
Conditional aggregation is the other common shape: group every innings by batter and count the ducks with SUM(CASE WHEN … THEN 1 ELSE 0 END). That keeps every batter, including those with a count of 0, so a HAVING … > 0 is needed to drop them. It is the better shape when you want several counts side by side (ducks, fifties, not-outs) in one pass.
Order by the count, highest first, and by name for equal counts. One scan and one grouping.
Reference solution (MySQL)
SELECT batter, COUNT(*) AS ducks
FROM Scorecard
WHERE runs = 0 AND dismissed = 1
GROUP BY batter
ORDER BY ducks DESC, batterAnother way
SELECT batter, SUM(CASE WHEN runs = 0 AND dismissed = 1 THEN 1 ELSE 0 END) AS ducks FROM Scorecard GROUP BY batter HAVING SUM(CASE WHEN runs = 0 AND dismissed = 1 THEN 1 ELSE 0 END) > 0 ORDER BY ducks DESC, batter← T20 League Fixtures Played on a Weekend in 2024 · Football League Table With Points and Goal Difference →