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 −
| product_id | product_name | category | price | stock_quantity |
|---|---|---|---|---|
| 101 | Laptop | Electronics | 1000.00 | 10 |
| 102 | Smartphone | Electronics | 800.00 | 20 |
| 103 | Office Chair | Furniture | 150.00 | 5 |
| 104 | Desk | Furniture | 250.00 | 8 |
And, the Orders table is as follows −
| order_id | product_id | quantity | order_date |
|---|---|---|---|
| 1 | 101 | 2 | 2023-11-01 10:00:00 |
| 2 | 103 | 1 | 2023-11-02 12:30:00 |
| 3 | 102 | 3 | 2023-11-03 15:45:00 |
| 4 | 103 | 2 | 2023-11-04 17:20:00 |
| 5 | 101 | 1 | 2023-11-05 14:00:00 |
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 −
SELECTCOUNT(*)AS total_orders FROM Orders;
It will fetch the following output −
| total_orders |
|---|
| 5 |
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 GROUPBY category;
It will fetch the following data from the Products table −
| category | product_count |
|---|---|
| Electronics | 2 |
| Furniture | 2 |
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 −
SELECTSUM(quantity)AS total_quantity_ordered FROM Orders;
It will fetch the following data from the Orders table −
| total_quantity_ordered |
|---|
| 9 |
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.
SELECTSUM(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 −
| total_revenue |
|---|
| 4900.00 |
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 −
SELECTAVG(price)AS average_price FROM Products;
It will fetch the following data −
| average_price |
|---|
| 550.00 |
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 −
SELECTAVG(quantity)AS average_quantity_per_order FROM Orders;
It will produce the following output −
| average_quantity_per_order |
|---|
| 1.80 |
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 −
SELECTMIN(price)AS cheapest_product,MAX(price)AS most_expensive_product FROM Products;
It will fetch the following data −
| cheapest_product | most_expensive_product |
|---|---|
| 150.00 | 1000.00 |
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 −
SELECTMIN(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 −
| first_order_date | last_order_date |
|---|---|
| 2023-11-01 10:00:00 | 2023-11-05 14:00:00 |
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 GROUPBY p.product_name;
It will produce the following output −
| product_name | total_quantity |
|---|---|
| Laptop | 3 |
| Smartphone | 3 |
| Office Chair | 3 |
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 GROUPBY p.category;
It will give you the following data as the output −
| category | total_revenue |
|---|---|
| Electronics | 4400.00 |
| Furniture | 500.00 |
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.
Leave a Reply