SQL Aggregate Functions

Introduction

In this chapter, we will focus on aggregate functions in SQL. Aggregate functions perform calculations on a set of values and return a single value. They are commonly used with the GROUP BY clause to summarize data. This chapter will cover the definition, types, syntax, and provide examples to help you understand how to use aggregate functions effectively.

What are Aggregate Functions?

Aggregate functions perform calculations on multiple values and return a single result. They are used to perform operations such as counting rows, calculating sums, averages, minimum and maximum values, and more.

Types of Aggregate Functions

1. COUNT(): Returns the number of rows that match a specified condition.
2. SUM(): Returns the total sum of a numeric column.
3. AVG(): Returns the average value of a numeric column.
4. MIN(): Returns the minimum value in a column.
5. MAX(): Returns the maximum value in a column.

Syntax for Aggregate Functions

Basic Syntax

SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition;

Using GROUP BY

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

Step-by-Step Example

Sample Table

First, let's create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Inserting Sample Data

INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('Ramesh', 'Kumar', 'HR', 50000),
('Sita', 'Patel', 'Finance', 60000),
('Arjun', 'Singh', 'HR', 55000),
('Priya', 'Sharma', 'Finance', 62000),
('Ravi', 'Verma', 'IT', 50000);

COUNT()

The COUNT() function returns the number of rows that match a specified condition.

Example

SELECT COUNT(*) AS total_employees
FROM employees;

Output

total_employees
5

Using COUNT() with GROUP BY

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

Output

department	total_employees
Finance	2
HR	2
IT	1

SUM()

The SUM() function returns the total sum of a numeric column.

Example

SELECT SUM(salary) AS total_salary
FROM employees;

Output

total_salary
277000

Using SUM() with GROUP BY

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Output

department	total_salary
Finance	122000
HR	105000
IT	50000

AVG()

The AVG() function returns the average value of a numeric column.

Example

SELECT AVG(salary) AS average_salary
FROM employees;

Output

average_salary
55400.00

Using AVG() with GROUP BY

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Output

department	average_salary
Finance	61000.00
HR	52500.00
IT	50000.00

MIN()

The MIN() function returns the minimum value in a column.

Example

SELECT MIN(salary) AS minimum_salary
FROM employees;

Output

minimum_salary
50000

Using MIN() with GROUP BY

SELECT department, MIN(salary) AS minimum_salary
FROM employees
GROUP BY department;

Output

department	minimum_salary
Finance	60000
HR	50000
IT	50000

MAX()

The MAX() function returns the maximum value in a column.

Example

SELECT MAX(salary) AS maximum_salary
FROM employees;

Output

maximum_salary
62000

Using MAX() with GROUP BY

SELECT department, MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

Output

department	maximum_salary
Finance	62000
HR	55000
IT	50000

Practical Examples

Example 1: Total Salary by Department

To find the total salary for each department:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Example 2: Average Salary by Department

To find the average salary for each department:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Example 3: Highest Salary in Each Department

To find the highest salary in each department:

SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

Conclusion

Aggregate functions are powerful tools for summarizing data in SQL. This chapter covered the different types of aggregate functions, their syntax, and provided examples to illustrate their use. Understanding how to use aggregate functions effectively will greatly enhance your ability to analyze and manipulate data in a relational database.