What are the Aggregate Functions in Oracle?

Aggregate Functions in Oracle

One of the most important tasks for generating insights while working with databases is summarising data, and aggregate functions are your go-to tools for this. Instead of calculating results for individual rows, these functions yield a single result for each group of rows. These functions are available in the Oracle Database and are frequently utilised in ORDER BY clauses, SELECT lists, and HAVING clauses.

Let’s look at a real-world example of these functions using a PRODUCTS table. We will first construct the table and add some sample data to it:

CREATE TABLE PRODUCTS (
    PRODUCT_ID NUMBER(5) PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(50),
    CATEGORY VARCHAR2(20),
    PRICE NUMBER(10, 2),
    STOCK_QUANTITY NUMBER(5)
);

INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (1, 'Laptop', 'Electronics', 1200.00, 50);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (2, 'Mouse', 'Electronics', 25.50, 200);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (3, 'Keyboard', 'Electronics', 75.00, 150);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (4, 'Monitor', 'Electronics', 300.00, NULL);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (5, 'Desk Chair', 'Furniture', 150.00, 80);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (6, 'Bookshelf', 'Furniture', 100.00, 30);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (7, 'Headphones', 'Electronics', 100.00, 120);
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, STOCK_QUANTITY) VALUES (8, 'Webcam', 'Electronics', NULL, 70);
COMMIT;

In this section, we will examine some of the most widely used aggregate functions:

COUNT

To find the number of rows, use the COUNT function. Due to the fact that it never returns NULL, it is a unique situation among aggregate functions.

• COUNT(*): The function COUNT(*) counts every row in a table, including duplicates and rows with all columns set to NULL. Instead of calculating the values of the columns, it counts the number of rows in the table.
• COUNT(column_name): This counts the number of rows where the specified column_name is not NULL.
• COUNT(DISTINCT column_name): This counts only the unique non-NULL values in the specified column.

SUM

The total of all values in a numeric expression for a collection of rows is returned by the SUM function. It returns the total after receiving as input a numeric datatype. The function returns NULL if an empty set is used.

SELECT SUM(PRICE) AS TotalPriceOfStockedItems FROM PRODUCTS;

Output:

TOTALPRICEOFSTOCKEDITEMS
------------------------
                 1850.50

You’ll see that the sum disregards the NULL values for PRICE in the “Monitor” and “Webcam” rows because aggregate functions typically don’t care about NULL values.

AVG

For a set of rows, the AVG function calculates the average of the values. Similar to SUM, it needs a numerical argument. When used on an empty set, NULL is returned.

SELECT AVG(PRICE) AS AveragePriceOfProducts FROM PRODUCTS;

Output:

AVERAGEPRICEOFPRODUCTS
----------------------
            308.416667

Once more, the average is calculated without taking into account NULL values in the PRICE field.

MAX

For a set of rows, the MAX function yields the maximum value of a given expression. It can be applied to date/time, character, or numeric datatypes, figuring out the “maximum” using the data type’s inherent sort order.

SELECT MAX(PRICE) AS HighestPriceItem FROM PRODUCTS;

Output:

HIGHESTPRICEITEM
----------------
         1200.00

NULL values are also ignored by MAX.

MIN

On the other hand, for a set of rows, the MIN function yields the expression’s smallest value. It operates with numeric, character, or date/time datatypes, just as MAX.

SELECT MIN(PRICE) AS LowestPriceItem FROM PRODUCTS;

Output:

LOWESTPRICEITEM
---------------
          25.50

NULL values are ignored by MIN, just like by other aggregate functions.

Handling NULL Values

It’s vital to remember that most aggregate functions ignore NULL values. This means that rows containing NULL in the column being aggregated are excluded from the calculation. For example, if you average five values: 1000, NULL, NULL, NULL, and 2000, the AVG function will ignore the NULLs and calculate the average as (1000+2000)/2 = 1500. The only aggregate function that doesn’t necessarily follow this rule is COUNT(*), which counts all rows regardless of NULLs. If an aggregate function is applied to a dataset with no rows, or only rows with NULLs as arguments, the function will return NULL (except for COUNT which returns zero).

Combining with GROUP BY and HAVING

Although a whole table can be utilised with aggregate functions (considering it as a single group), the GROUP BY clause is frequently required to fully utilise them. Based on the values of one or more columns, GROUP BY separates the rows of a queried table into subgroups. The aggregate function is then applied to each of these subgroups.

Like a WHERE clause filters individual rows, the HAVING clause filters these grouped results, but it applies its logic only to group function results.

For example, to find the average price and total stock quantity for each product category:

SELECT
    CATEGORY,
    AVG(PRICE) AS AveragePrice,
    SUM(STOCK_QUANTITY) AS TotalStock
FROM
    PRODUCTS
GROUP BY
    CATEGORY
HAVING
    SUM(STOCK_QUANTITY) > 100;

Output:

CATEGORY             AVERAGEPRICE TOTALSTOCK
-------------------- ------------ ----------
Electronics             400.83333        440

In this example, the PRODUCTS are grouped by CATEGORY, then the AVG price and SUM of stock are calculated for each category. The HAVING clause then filters these groups, showing only categories where the TotalStock is greater than 100. In contrast to Electronics (50+200+150+120+70 = 590, before accounting for nulls), the “Furniture” category, which has a total stock of 110 (80+30), would be eliminated if its average price was not required or if the having clause filtered it out.

Although the monitors and webcams in this case have NULL stock, their price is still taken into account for the average, if not null, even though their stock is not included in the total. Making the following corrections to the ‘Electronics’ calculation using the STOCK_QUANTITY values: 50 (laptop) + 200 (mouse) + 150 (keyboard) + 120 (headphones) + 70 (webcam) = 590. Due to its NULL stock, the monitor is not included in SUM(STOCK_QUANTITY).

Comprehending and utilising aggregate functions, particularly when combined with GROUP BY and HAVING clauses, is essential for carrying out complex data analysis and producing insightful reports from your database. It’s similar to having a powerful calculator that can use a few terms to summarise whole spreadsheets.