SQL MODE()

Beacon Robotics wants to know the single most common job title within a department, not its average or its middle value, but the one value that shows up more than any other. That's the mode.

Learning Objectives

Define the mode, and explain how it differs from both the mean and the median.
Use PostgreSQL's mode() and Oracle's STATS_MODE(), two real native functions under genuinely different names and syntax.
Build the manual GROUP BY/COUNT equivalent for the four databases with no native mode function at all.

Group by Value

Count occurrences of each distinct value

Find the Highest Count

The most frequent single value

Mode

Ties are possible, and real

The Two Real Native Functions

Database	Finding the Mode
PostgreSQL	mode() WITHIN GROUP (ORDER BY col), an ordered-set aggregate, the same WITHIN GROUP family as PERCENTILE_CONT.
Oracle	STATS_MODE(col), a plain aggregate, no WITHIN GROUP clause needed at all, genuinely different syntax from PostgreSQL's.
MySQL / MariaDB / SQL Server / SQLite	No native mode function, built manually with GROUP BY, COUNT, and an ORDER BY ... DESC LIMIT 1 (or TOP 1 on SQL Server).

The Manual Equivalent

Group by the value being measured, count how many rows fall into each group, sort by that count descending, and take the single highest row. This same pattern works on every database here, regardless of whether it has a dedicated mode function or not.

MySQL / MariaDB / PostgreSQL / SQLite

SELECT department_id, COUNT(*) AS occurrences
FROM employees
GROUP BY department_id, department_id
ORDER BY occurrences DESC
LIMIT 1;

SQL Server

SELECT TOP 1 department_id, COUNT(*) AS occurrences
FROM employees
GROUP BY department_id
ORDER BY occurrences DESC;

Try It Yourself

Find the most frequently occurring department_id across all employees, on PostgreSQL, without using mode().

Answer

SELECT department_id, COUNT(*) AS occurrences
FROM employees
GROUP BY department_id
ORDER BY occurrences DESC
LIMIT 1;

Frequently Asked Questions

What happens when a group has more than one equally frequent value, a tie for the mode?

Native functions and the manual GROUP BY/COUNT/LIMIT 1 pattern both just return one of the tied values, usually whichever sorts first, rather than signaling that a tie exists. If detecting a tie actually matters, check whether more than one value shares the single highest count first.

Can the mode be computed on a non-numeric column, like a text job title?

Yes, mode is defined for any value that can be grouped and counted, numeric or not, unlike mean or median, which genuinely require numeric (or at least orderable) values.

Common Beginner Mistakes

Writing mode() on Oracle, or STATS_MODE() on PostgreSQL

Neither works, the two real native functions are database-specific, with genuinely different syntax besides the name, PostgreSQL's mode() needs a WITHIN GROUP (ORDER BY ...) clause, Oracle's STATS_MODE() doesn't take one at all.

Forgetting that a tie for the mode is silently resolved, not reported

Both the native functions and the manual pattern just return one winning value when multiple values are equally frequent, without flagging that a tie happened, check the counts directly first if that distinction matters for the specific question being asked.

Best Practices

Use the manual GROUP BY/COUNT/ORDER BY DESC/LIMIT 1 (or TOP 1) pattern for portability across databases that lack a native mode function, it's the same logic everywhere.
Check for a tie explicitly (more than one value sharing the top count) before assuming the single returned value is uniquely the mode.
Remember mode works on non-numeric columns too, it's valid for a categorical column like job title, not just numeric data.

Interview Questions

How does mode differ from mean and median, and give a case where it's the right statistic to report.

Mean is the average, median is the middle value once sorted, mode is simply the single most frequently occurring value. Mode is the right choice for categorical data where averaging or sorting numerically doesn't make sense, like finding the most common job title or the most common department size, where mean and median aren't meaningful at all.

Write the portable GROUP BY pattern that finds the mode on a database with no dedicated mode function.

SELECT col, COUNT(*) AS occurrences FROM t GROUP BY col ORDER BY occurrences DESC LIMIT 1; (TOP 1 instead of LIMIT 1 on SQL Server) — group by the value itself, count how many rows share each value, sort descending by that count, and take the single highest row.

Summary

Mode is the single most frequent value in a group, meaningful for categorical data where mean and median aren't. PostgreSQL's mode() and Oracle's STATS_MODE() are two real, differently-shaped native functions, the other four databases need the same portable GROUP BY/COUNT/ORDER BY DESC/LIMIT 1 pattern instead.

What's Next?

PERCENTILE_CONT and PERCENTILE_DISC in full, the general machinery behind this module's median lesson, for any percentile, not just the 50th.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.

Or try 50 SQL practice questions →