Batters Who Were Out for a Duck This Season

Easy
455.

Batters Who Were Out for a Duck This Season

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

ColumnType
innings_idint
match_idint
battervarchar
runsint
balls_facedint
dismissedbool

Primary key: innings_id.

One row per batter per innings. dismissed is 1 if the batter was out and 0 if not out.

Example

The rows must come back in the order shown.

Scorecard

innings_idmatch_idbatterrunsballs_faceddismissed
1301Rohan Iyer011
2301Kabir Khan020
3302Rohan Iyer34221
4302Arjun Nair041
5303Rohan Iyer031
6303Kabir Khan011
7303Dev Mehta51300

Output

batterducks
Rohan Iyer2
Arjun Nair1
Kabir Khan1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
innings_idint
match_idint
battervarchar
runsint
balls_facedint
dismissedbool

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_idmatch_idbatterrunsballs_faceddismissed
1301Rohan Iyer011
2301Kabir Khan020
3302Rohan Iyer34221
4302Arjun Nair041
5303Rohan Iyer031
6303Kabir Khan011
7303Dev Mehta51300

Output

batterducks
Rohan Iyer2
Arjun Nair1
Kabir Khan1

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, batter

Another 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 →