SQL Aggregate Functions & Clauses –
Detailed Notes
1. Aggregate Functions
Aggregate functions in SQL provide a way to perform a calculation on multiple values and
return a single value. These functions are commonly used with SELECT statements.
Function Definition Syntax Example Explanation/Result
MAX() Returns the maximum SELECT MAX(salary) Returns the highest
(largest) value in a FROM employees; salary in the employees
specified column. table.
MIN() Returns the minimum SELECT MIN(age) Returns the lowest age
(smallest) value in a FROM users; among all users.
specified column.
AVG() Returns the average SELECT AVG(marks) Calculates the average
(arithmetic mean) of FROM students; marks of all students.
values in a specified
column.
SUM() Returns the total sum SELECT SUM(sales) Adds up all sales values
of values in a specified FROM orders; from orders.
column.
COUNT() Returns the number of SELECT COUNT(dept) Counts number of non-
non-NULL values in a FROM staff; NULL department
specified column. values.
COUNT(*) Returns the total SELECT COUNT(*) Counts all rows in the
number of rows in the FROM staff; staff table.
result, including rows
with NULL values.
Example Output:
Suppose an employee table:
id name salary
1 Alice 70000
2 Bob 80000
3 Carol 75000
SELECT MAX(salary) FROM employee; returns 80000
SELECT COUNT(*) FROM employee; returns 3
2. GROUP BY Clause
Definition:
GROUP BY is used to arrange identical data into groups. This is especially useful when
combined with aggregate functions, so calculations can be made on each group of data.
Syntax:
SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1;
Example:
SELECT dept, AVG(salary)
FROM employees
GROUP BY dept;
This groups employees by their department and computes the average salary for each
department.
3. HAVING Clause
Definition:
HAVING is used to filter groups after GROUP BY has been performed. It works like the
WHERE clause, but WHERE cannot filter results of aggregate functions—HAVING can.
Syntax:
SELECT column, AGG_FUNC(column2)
FROM table
GROUP BY column
HAVING condition;
Example:
SELECT dept, COUNT(*)
FROM employees
GROUP BY dept
HAVING COUNT(*) > 10;
This shows only departments with more than 10 employees.
4. ORDER BY Clause
Definition:
ORDER BY is used to sort the result-set using one or more columns, either in ascending
(ASC, default) or descending (DESC) order.
Syntax:
SELECT column1, column2
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
Example:
SELECT name, salary
FROM employees
ORDER BY salary DESC;
Returns the name and salary of employees sorted from highest to lowest salary.
5. Working with Two Tables – Equi-Join
Definition:
An Equi-Join is a type of join that combines rows from two or more tables based on a
condition that their specified columns are equal. This helps in retrieving related data
stored across tables.
Syntax:
SELECT a.column1, b.column2
FROM table1 a
JOIN table2 b
ON a.common_column = b.common_column;
Example:
SELECT s.id, s.name, d.dept_name
FROM staff s
JOIN department d
ON s.dept_id = d.id;
This joins the staff and department tables and shows the staff member’s ID, name, and the
corresponding department name.
Summary Table
Concept Definition Example Syntax Example Output
MAX, MIN Highest/lowest value in SELECT MAX(score) 99
a column FROM test;
AVG Arithmetic mean of SELECT AVG(price) 19.5
column values FROM products;
SUM Total sum of column SELECT SUM(quantity) 1500
values FROM orders;
COUNT, * Number of (non-null/all) SELECT COUNT(*) 12
rows FROM info;
GROUP BY Groups rows for GROUP BY category [Electronics:10,
calculations Clothing:5]
HAVING Filters groups after HAVING SUM(sales) > Only groups with sales
aggregation 1000 > 1,000
ORDER BY Sorts query results ORDER BY age DESC Results sorted from
ascending/descending oldest to youngest
Equi-Join Combines rows from JOIN ON a.x = b.x Employees with
two tables on matching department name
column values
With these details, you can write and understand most commonly used SQL aggregate
queries, filters, sorting commands, and basic joins. Each clause enables powerful analysis
and reporting from your database tables. If you’d like real data examples or more
advanced join types, let me know!
