SQL Aggregate Functions Tutorial 🎯

Welcome to our SQL Aggregate Functions tutorial! In this comprehensive guide, we'll explore how to use these powerful tools to perform calculations on sets of data and make your SQL queries more efficient. Let's dive in! 🐠

What are SQL Aggregate Functions? 📝

SQL aggregate functions are used to perform calculations, such as counting, summing, averaging, and finding the minimum or maximum values, on a group of records within a table. They are essential when you want to analyze and summarize data in your database.

Basic Aggregate Functions 💡

COUNT()

The COUNT() function returns the number of rows in a result set. Here's an example:

sql

SELECT COUNT(*) FROM users;

In this example, COUNT(*) counts the number of records in the users table.

SUM()

The SUM() function adds up the values of a specified column.

sql

SELECT SUM(balance) FROM accounts;

In this example, SUM(balance) calculates the total balance in the accounts table.

AVG()

The AVG() function calculates the average value of a specified column.

sql

SELECT AVG(age) FROM employees;

In this example, AVG(age) finds the average age of employees in the employees table.

MIN() and MAX()

The MIN() and MAX() functions return the minimum and maximum values of a specified column, respectively.

sql

SELECT MIN(id), MAX(id) FROM users;

In this example, MIN(id) and MAX(id) find the smallest and largest IDs in the users table.

Grouping Rows 💡

To group rows, you can use the GROUP BY clause. This allows you to perform aggregate functions on specific groups of data.

sql

SELECT department, COUNT(*) FROM employees GROUP BY department;

In this example, COUNT(*) counts the number of employees in each department.

Advanced Aggregate Functions 💡

GROUP_CONCAT()

The GROUP_CONCAT() function combines the values of a specified column into a single string, separated by a specified delimiter.

sql

SELECT name, GROUP_CONCAT(skills SEPARATOR ', ') FROM employees GROUP BY name;

In this example, GROUP_CONCAT(skills SEPARATOR ', ') combines the skills of each employee into a single string, separated by commas.

DISTINCT and COUNT(DISTINCT) 💡

The DISTINCT keyword returns only unique values from a specified column. The COUNT(DISTINCT) function counts the number of unique values in a specified column.

sql

SELECT COUNT(DISTINCT country) FROM customers;

In this example, COUNT(DISTINCT country) counts the number of unique countries among the customers.

Quiz 💡

That's it for our SQL Aggregate Functions tutorial! By now, you should have a solid understanding of these powerful tools. Happy coding! 🐠