Group Operators in Oracle: Function and Purpose

In Oracle SQL, group operators (also called group functions or aggregate functions) operate on a set of rows and return a single result per group. Instead of looking at each row individually, they summarize data, making it easier to answer questions such as:

• What is the total sales amount per customer?
• What is the average order value this month?
• What is the highest or lowest value in a column?

Group operators are typically used together with GROUP BY and optionally HAVING. In modern Oracle releases (including Oracle Database 19c and 23ai), these functions remain the foundation for summarizing data in reports, dashboards, and ad hoc queries.

At a high level, group operators help you:

1. Aggregate data from detailed rows into concise summaries.
2. Highlight trends such as minimum, maximum, and average values.
3. Count occurrences of rows or values in a column.
4. Measure dispersion using standard deviation and variance.

When you combine rows and display summarized data, you are using group functions. The table below shows the most common group functions available in Oracle:

Group function	Description
SUM(expr)	Adds up a total amount for the column or expression in expr.
MAX(expr)	Returns the maximum value of expr.
MIN(expr)	Returns the minimum value of expr.
AVG(expr)	Returns the average of expr, ignoring NULL values.
COUNT(expr)	Counts rows where expr is not NULL. When used as COUNT(*), it counts all rows in the group.
STDDEV(expr)	Calculates the standard deviation of expr, showing how spread out the values are.
VARIANCE(expr)	Calculates the variance of expr, another measure of spread.

These functions ignore NULL values (except for COUNT(*), which counts every row). You can apply them to numeric columns, and in some cases (such as MIN/MAX) to character columns as well.

Here is a typical query that adds up sales by customer:

SELECT FIRSTNAME,
       LASTNAME,
       SUM(TOTAL_SALE_AMOUNT) AS customer_total
FROM   CUSTOMER       C
       JOIN CUSTOMER_SALE CS
         ON C.CUST_ID = CS.CUST_ID
GROUP  BY FIRSTNAME, LASTNAME
ORDER  BY FIRSTNAME, LASTNAME;
  

The GROUP BY clause defines how SUM(TOTAL_SALE_AMOUNT) groups the rows. In this example, each distinct combination of FIRSTNAME and LASTNAME produces one summary row showing the total sales for that customer.

Important rules to remember:

• Every non-aggregated column in the SELECT list must appear in the GROUP BY clause.
• GROUP BY controls grouping; ORDER BY controls presentation order.
• You can group by expressions as well as columns (for example, GROUP BY TRUNC(order_date)).

Although ORDER BY is not required, it is common to add an ORDER BY clause after GROUP BY so that summary rows are returned in a predictable and readable order (for example, by customer name, by region, or by date).

SELECT   FIRSTNAME,
         LASTNAME,
         SUM(TOTAL_SALE_AMOUNT) AS customer_total
FROM     CUSTOMER       C
         JOIN CUSTOMER_SALE CS
           ON C.CUST_ID = CS.CUST_ID
GROUP BY FIRSTNAME, LASTNAME
HAVING   SUM(TOTAL_SALE_AMOUNT) > 500
ORDER BY FIRSTNAME, LASTNAME;

Most group functions accept an optional DISTINCT or ALL keyword. This is easiest to see with COUNT, which has the following format:

COUNT([DISTINCT | ALL] value)
  

Consider the following example:

SELECT COUNT(DISTINCT City) AS distinct_cities,
       COUNT(City)          AS non_null_cities,
       COUNT(*)             AS total_rows
FROM   COMFORT;
  

Sample output:

DISTINCT_CITIES  NON_NULL_CITIES  TOTAL_ROWS
---------------  ---------------  ----------
2                8                8
  

Here is what each expression does:

• COUNT(DISTINCT City) counts only unique city names where City is not NULL. If there are eight rows but only two different city values, the result is 2.
• COUNT(City) counts how many rows have a non-null value in City. It does not remove duplicates.
• COUNT(*) counts every row in the table or group, even if all columns in a row are NULL.

Other group functions also support DISTINCT:

• SUM(DISTINCT amount) – sums only distinct non-null amount values.
• AVG(DISTINCT rating) – averages each unique non-null rating once.

These options are useful when duplicate values would otherwise distort your summary (for example, counting unique customers rather than counting the number of orders).

The next lesson introduces three advanced group operators that build on these foundations and allow you to summarize data in more flexible ways.

[1]Group function: A predefined function (such as SUM, AVG, COUNT, MIN, or MAX) that combines multiple rows into a single summarized value for each group of rows defined by GROUP BY.