Oracle STATS_MODE Function

Which airport are most employees of a department based at? Which cabin does a customer usually book? The answer is the mode, the most frequent value. STATS_MODE returns it in one aggregate call, for values of any type, without the GROUP BY, COUNT, and ranking that the question would otherwise need.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

stats_mode(expr)

STATS_MODE ignores NULLs and returns the value that occurs most often in the group. When several values are equally frequent, it returns one of them.

The Usual Base Airport

The last column of this query finds the base airport that occurs most often in each department.

Example:

select department_id, count(*) as staff, median(salary) as median_salary,
       round(avg(salary)) as avg_salary, stats_mode(base_airport) as usual_base
from   employees
where  department_id in (20, 30, 40)
group  by department_id;

Output:

   DEPARTMENT_ID    STAFF    MEDIAN_SALARY    AVG_SALARY USUAL_BASE
________________ ________ ________________ _____________ _____________
              20       12            17000         17817 DXB
              30       11             6900          9009 DXB
              40       10            13400         14210 DXB

Dubai, the airline's hub, is the usual base of all three departments.

The Usual Cabin

Example:

-- the usual cabin of three customers
select b.customer_id, stats_mode(t.cabin) as usual_cabin, count(*) as tickets
from   bookings b join tickets t on t.booking_id = b.booking_id
where  b.customer_id in (1, 12, 85)
group  by b.customer_id
order  by b.customer_id;

Output:

   CUSTOMER_ID USUAL_CABIN       TICKETS
______________ ______________ __________
             1 ECONOMY                18
            12 BUSINESS               16
            85 BUSINESS               16

Customer 1 usually flies economy, and customers 12 and 85 business.

Things to Know

• With ties, the result is one of the tied values, and which one is not guaranteed. If ties matter, count the values with GROUP BY and rank them with RANK.
• STATS_MODE works on text, numbers, and dates alike.
• It returns the value only; to also show how often it occurs, count it separately.

Related Guides

• Oracle MEDIAN Function
• Oracle COUNT Function

Conclusion

STATS_MODE returns the most frequent value of a group, of any data type. Use it for "usual" values such as a typical airport or cabin, and count and rank the values yourself when ties must be reported.