How to Apply Aggregate Functions in SQL: SUM, AVG, COUNT etc. A Beginner's Guide
Introduction
Aggregate functions in SQL allow analysis and summarization of data from which developers and data analysts get the ability to compute cross-row calculations. For a large dataset, with one or more columns as such, these aggregate functions give easy access to retrieving useful insights. In this blog, we'll review one by one all aggregate functions, along with an example to illustrate how each actually works and can be put into real-world usage.
Table of Contents
1. What Are Aggregate Functions in SQL?
What Is an Aggregate Function?
When to Use Aggregate Functions?
Types of Aggregate Functions
Some Examples of Aggregate Functions in SQL
2. Types of Aggregate Functions in SQL
SUM()
AVG()
COUNT()
MAX()
MIN()
3. Using Aggregate Functions with GROUP BY
4. Combining Aggregate Functions with HAVING
5. Practical Examples of Aggregate Functions in Action
6. Conclusion
---------------------------------------
1. What Are Aggregate Functions in SQL?
Aggregate functions accept a list of values, compute something with them, and return one value. Aggregate functions can be used in conjunction with GROUP BY to sum up different groups of data, or they can be used as standalone aggregate functions when computing the summary for an entire table. Common aggregate function use cases include calculating overall sales totals, finding averages scores, counting records, and identification of the maximum or minimum values in a dataset.
2. Aggregate Functions in SQL
SUM()
The SUM() function is used to get the total sum of a numeric column. It's usually used in financial or sales data analysis where you want to get the overall revenue or expenses.
Syntax:
SELECT SUM(column_name) FROM table_name;
Calculate the total sales in the sales table.
SELECT SUM(amount) AS total_sales FROM sales;
AVG() function
AVG() will calculate an average value for a given numeric column. That means we are calculating the mean price, mean score, and other types of averages.
Syntax
SELECT AVG(column_name) FROM table_name;
Find the average sale amount
SELECT AVG(amount) AS average_sale FROM sales;
COUNT()
This returns the count of the rows meeting the condition in the selection. We will use this to count orders, customer records, transactions, or even the list of things.
Syntax:
SELECT COUNT(column_name) FROM table_name;
Example: To count the number of orders in an orders table:
SELECT COUNT(order_id) AS order_count FROM orders;
> Note: COUNT(*) counts all rows in a table, including duplicates and nulls.
MAX()
The MAX() function finds the highest value in a specified column. It's helpful for finding maximum prices, scores, dates, etc.
Syntax:
SELECT MAX(column_name) FROM table_name;
Example: To find the highest sale amount:
SELECT MAX(amount) AS highest_sale FROM sales;
MIN()
Returns the lowest value within the given column. It can be used for minimum prices, scores, dates, and more.
Syntax is given below:
SELECT MIN(column_name) FROM table_name;
For example finding low sales amount is given by:
SELECT MIN(amount) AS lowest_sale FROM sales;
With aggregate functions combined with the use of GROUP BY clause, it is then possible to create calculations about subsets of your data, including total sales by region or average salary by department. Using the GROUP BY clause creates groups of summary rows by stacking rows of a table based on criteria specified in columns.
Select region and use SUM with amount AS alias total sales from the table sales; GROUP BY region;
In this case, for each region, the value of SUM(amount) is grouped by the region column.
4. Aggregate Functions and HAVING Clause
You can employ the HAVING clause to filter for groups based on the results generated by GROUP BY. Such a scenario can be highly useful when you want some groups to be included but others to be excluded by the result of an aggregate function.
Example: List regions with total sales above 50,000.
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 50000;
Here, HAVING filters out those geographies where the sum of the sales is $50,000 or less.
5. Aggregation Functions in Life
Example 1: How Many Orders per Customer?
Assume we are developing a kind of e-commerce application in which we would like to display the number of total orders placed by each customer.
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;
Example 2: What is average Salary in each Department?
For an HR database, we could calculate the average salary for each department.
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
Example 3: Maximum and Minimum Sales by Salesperson
To know how each salesperson is performing, you might want to know the maximum and minimum sales they made.
SELECT salesperson_id, MAX(sale_amount) AS max_sale, MIN(sale_amount) AS min_sale
FROM sales
GROUP BY salesperson_id;
Example 4: Total Revenue with Minimum Order Amount
Let's assume we want to find the total revenue from only those orders that exceeded $100.
SELECT SUM(amount) AS total_revenue
FROM sales
WHERE amount > 100;
Conclusion
The use of aggregate functions makes data analysis more direct in SQL as it helps a person get quick and direct insights into data. Adding up sums and counting all the way up to averages, aggregate functions facilitate heavy chunks of data, providing clues on trends in information which aid in decision making using data. Such aggregate functions coupled with GROUP BY and HAVING clauses only improve further the flexibility and mastery control over data aggregation to tackle real analytics jobs.
Mastering aggregate functions and how to use them with other SQL clauses will take you a long way in working with databases. The practice of these queries gets you started on building those more advanced analytical skills in SQL.
Happy querying!
