DISTINCT, and the three COUNTs

DISTINCT removes duplicate rows from a result. Its close cousins are the three ways of counting, which give three different answers on the same column:

Part 1: counting three ways

The test case

How many players are there, how many of them bowl, and how many different bowling styles are there?

Way 1: the three COUNTs

select count(*)                       as players,
       count(bowling_style)           as bowlers,
       count(distinct bowling_style)  as styles
from   cr_players;

Specialist batters and keepers have no bowling style (null), so count(bowling_style) skips them.

Way 2: DISTINCT in a subquery, and CASE

select (select count(*) from cr_players)                                         as players,
       (select sum(case when bowling_style is not null then 1 else 0 end)
        from   cr_players)                                                       as bowlers,
       (select count(*) from (select distinct bowling_style from cr_players
                              where  bowling_style is not null))                 as styles
from   dual;

The long way, to show what each COUNT means. Note the where bowling_style is not null in the last one: SELECT DISTINCT keeps null as a value of its own, but count(distinct ...) ignores it.

Part 2: DISTINCT across columns

The test case

In 2025, how many matches did each team play, and at how many different grounds?

Way 1: COUNT(DISTINCT)

with team_matches as (
  select season, venue_id, home_team_id as team_id from cr_matches
  union all
  select season, venue_id, away_team_id from cr_matches
)
select t.team_code, count(*) as matches, count(distinct m.venue_id) as grounds
from   team_matches m
join   cr_teams t on t.team_id = m.team_id
where  m.season = 2025
group  by t.team_code
order  by t.team_code;

Way 2: DISTINCT first, then count

with team_matches as (
  select season, venue_id, home_team_id as team_id from cr_matches
  union all
  select season, venue_id, away_team_id from cr_matches
),
grounds as (
  select distinct team_id, venue_id from team_matches where season = 2025
)
select t.team_code,
       (select count(*) from team_matches x where x.team_id = t.team_id and x.season = 2025) as matches,
       count(*) as grounds
from   grounds g
join   cr_teams t on t.team_id = g.team_id
group  by t.team_code, t.team_id
order  by t.team_code;

SELECT DISTINCT team_id, venue_id removes duplicate pairs: Durban at Umgeni Oval three times becomes one row. Counting those rows per team gives the grounds. DISTINCT always applies to the whole row, never to one column.

Durban played six matches at only three grounds: home advantage, Dhows-style.

When "about right" is good enough: APPROX_COUNT_DISTINCT

COUNT(DISTINCT ...) has to remember every value it has seen, which gets expensive on billions of rows. APPROX_COUNT_DISTINCT estimates the count instead (with an algorithm called HyperLogLog), using a tiny, fixed amount of memory:

select count(distinct batter_id)        as exact_batters,
       approx_count_distinct(batter_id) as approx_batters
from   cr_deliveries;

On 66 batters the estimate will almost certainly be exact. On a billion web clicks it's typically within a percent or two and many times faster. Use it for dashboards and "how many unique visitors, roughly?"; never for an invoice.

19c and 26ai

The same on both.

Gotchas

• DISTINCT is not a function. select distinct(a), b is select distinct a, b: the brackets do nothing, and it still de-duplicates the whole row.
• DISTINCT to fix duplicates from a bad join hides the bug instead of fixing it. Find out why the rows doubled.
• count(distinct a, b) isn't allowed; count the distinct pairs in a subquery, as in Way 2.