DBMS - Aggregate Functions in SQL

---

---

In SQL querying sometimes we use special functions called aggregate functions that let us perform calculations on our data to find summaries, totals, averages, and other meaningful insights. If we have ever wanted to know the total sales for a month, the average rating of a product, or the number of customers who placed an order, then we would have to use the aggregate functions. Read this chapter to learn how to use aggregate functions in SQL.

Sample Tables and Data

We will take two sample tables and a set of examples to show how aggregate functions work. Here we are using the following schema and data from an e-commerce setup. The two tables we are going to use are Orders and Products.

Here is the Products table −

And, the Orders table is as follows −

Aggregate Functions in SQL

Aggregate functions in SQL include the following functions −

• The COUNT function
• The SUM function
• The AVG function
• The MIN and MAX Functions

In the following sections of this chapter, we will have examples to demonstrate how you can apply these aggregate functions in SQL.

The COUNT Function: Counting the Rows

The COUNT function is used to find the number of rows in a table or the number of records that match a condition. It is like counting the items in a shopping cart.

Example: Total Number of Orders

In this query, we have use the aggregate function COUNT to get the total number of orders from the Orders table −

SELECT COUNT(*) AS total_orders
FROM Orders;

It will fetch the following output −

Here, we can see it is simply counting all the rows in the Orders table. The result tells us there are five orders in the Orders table.

Example: Count the Products in Each Category

Use the following query to count the products in each category −

SELECT category, COUNT(*) AS product_count
FROM Products
GROUP BY category;

It will fetch the following data from the Products table −

Here, this query is grouping the products by category and counting how many products are there in each category.

The SUM Function: Adding the Values

The SUM function calculates the total of a numeric column. It is quite useful in finding totals, like sales or stock quantities.

Example: Total Quantity of Products Ordered

Use the following query to get the total quantity of products ordered −

SELECT SUM(quantity) AS total_quantity_ordered
FROM Orders;

It will fetch the following data from the Orders table −

The SUM function adds up the quantity column in the Orders table. It gives us the total number of products ordered across all orders.

Example: Total Revenue Generated

To calculate the total revenue generated, we need to join the Orders and Products tables and multiply the quantity by price for each product.

SELECT SUM(o.quantity * p.price) AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id;

Here, we will get the following output −

The above query simply multiplies the quantity of each order by the price of the corresponding product. Then, it adds up the results to calculate the total revenue.

The AVG Function: Calculating Averages

The AVG function calculates the average value of a numeric column. It’s useful for finding the trends or understanding typical values.

Example: Average Price of Products

You can use the following query to get the average price of products in the Products table −

SELECT AVG(price) AS average_price
FROM Products;

It will fetch the following data −

The AVG function gives the average price of all the products in the Products table.

Example: Average Quantity Ordered per Order

Use the AVG function as shown in the following query to get the average quantity ordered per order −

SELECT AVG(quantity) AS average_quantity_per_order
FROM Orders;

It will produce the following output −

In this query, we used the AVG function to calculate the average number of items ordered in each order.

The MIN and MAX Functions: Finding the Minimum and Maximum Values

The MIN and MAX functions are used to find the smallest and largest values in a column.

Example: Cheapest and Most Expensive Products

Use the following query to find the cheapest and the most expensive products in the Products table −

SELECT MIN(price) AS cheapest_product, MAX(price) AS most_expensive_product
FROM Products;

It will fetch the following data −

Here, we can see that the MIN function finds the lowest price and the MAX function finds the highest price.

Example: Earliest and Latest Order Dates

Here is another example of how you can use the MIN and MAX functions to get the earliest and latest order dates −

SELECT MIN(order_date) AS first_order_date, MAX(order_date) AS last_order_date
FROM Orders;

It will fetch the following data from the Orders table −

Here, this query shows the earliest and most recent dates when orders were placed.

The GROUP BY Clause: Aggregating Data by Groups

The GROUP BY clause works with the aggregate functions to organize data into groups. Think of it as sorting the data into buckets before performing calculations.

Example: Total Quantity Ordered per Product

You can use the following query to find the total quantity ordered per product −

SELECT p.product_name, SUM(o.quantity) AS total_quantity
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.product_name;

It will produce the following output −

This query groups the orders by product and then calculates the total quantity ordered for each.

Example: Revenue per Category

Use the following query to calculate the revenue per each product category −

SELECT p.category, SUM(o.quantity * p.price) AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.category;

It will give you the following data as the output −

Here, we are grouping the products by category and then calculating the total revenue for each.

Importance of Aggregate Functions

Aggregate functions help us perform data analysis. Instead of going through each of the records manually, we can use aggregate functions to quickly find totals, averages, and other key metrics with just a few lines of SQL. Aggregate functions are quite useful in creating reports or dashboards that summarize large datasets.

Conclusion

In this chapter, we explained how to use aggregate functions in SQL. We used several examples to demonstrate the usage of the aggregate functions such as COUNT, SUM, AVG, MIN and MAX. In addition, we touched upon the process of combining these aggregate functions using the GROUP BY clause to analyze grouped data.