Day 4: Mastering Aggregate Functions in SQL: COUNT, SUM, AVG, MAX, and MIN

Follow for content on Investing : LinkedIn / Instagram

Follow for content on Technical: LinkedIn

Photo by Pew Nguyen: https://www.pexels.com/photo/a-keyboard-on-a-desk-17350369/

Introduction

SQL aggregate functions are used to summarize data by performing calculations on multiple rows. They are a powerful tool for data analysis and can be used to answer a wide variety of questions, such as:

• How many customers do we have?
• What is the total revenue for the month?
• What is the average order value?
• What is the highest-selling product?
• What is the lowest-priced product?

In this blog post, we will discuss the most common SQL aggregate functions, as well as the GROUP BY and HAVING clauses. We will also provide examples of how to use these functions and clauses to summarize and analyze data.

Common SQL Aggregate Functions

The following are some of the most common SQL aggregate functions:

• COUNT: The COUNT function returns the number of rows in a table or column. For example, the following query will return the total number of customers in the customers table:

SELECT COUNT(*) FROM customers;

• SUM: The SUM function returns the sum of all the values in a column. For example, the following query will return the total revenue for the month of October 2023:

SELECT SUM(revenue) FROM orders WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';

• AVG: The AVG function returns the average value in a column. For example, the following query will return the average order value for the month of October 2023:

SELECT AVG(order_value) FROM orders WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';

• MAX: The MAX function returns the highest value in a column. For example, the following query will return the highest-selling product in the products table:

SELECT product_name FROM products ORDER BY sales_quantity DESC LIMIT 1;

• MIN: The MIN function returns the lowest value in a column. For example, the following query will return the lowest-priced product in the products table:

SELECT product_name FROM products ORDER BY price ASC LIMIT 1;

The GROUP BY Clause

The GROUP BY clause is used to group rows together based on the values in a column. Once the rows have been grouped, you can use aggregate functions to summarize the data for each group.

For example, the following query will group customers by country and then calculate the total revenue for each country:

SELECT country, SUM(revenue) AS total_revenue
FROM orders
GROUP BY country;

This query will return a result set with one row for each country. The country column will contain the name of the country and the total_revenue column will contain the total revenue for that country.

The HAVING Clause

The HAVING clause is used to filter the results of a GROUP BY query. It works just like a WHERE clause, but it is applied to the groups of rows instead of the individual rows.

For example, the following query will group customers by country and then calculate the total revenue for each country. It will then filter the results to only include countries with a total revenue of over $1 million:

SELECT country, SUM(revenue) AS total_revenue
FROM orders
GROUP BY country
HAVING total_revenue > 1000000;

This query will return a result set with one row for each country with a total revenue of over $1 million.

Conclusion

SQL aggregate functions are a powerful tool for data analysis. By using aggregate functions and the GROUP BY and HAVING clauses, you can summarize and analyze your data in a variety of ways.