SQL Tutorials

Grouping and Aggregating Data in SQL

Learn how to group and aggregate data using SQL functions like COUNT, SUM, AVG, MAX, and MIN with the GROUP BY clause.

Main Table: Employees

Here is the main employees table used for many of the queries below:

Employee ID	Name	Department	City	Salary
1	Alice	IT	New York	$75,000
2	Bob	Sales	Los Angeles	$65,000
3	Charlie	HR	Chicago	$50,000
4	David	IT	New York	$80,000
5	Eva	HR	Los Angeles	$55,000

1. Using the GROUP BY Clause

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns. It's often used with aggregate functions to summarize data.

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

This query counts the number of employees in each department.

Department	Count
HR	2
IT	2
Sales	1

Additional Example:

SELECT city, AVG(salary) 
FROM employees 
GROUP BY city;

This query calculates the average salary for employees grouped by their city.

City	Average Salary
New York	$77,500
Los Angeles	$60,000
Chicago	$50,000

2. COUNT Function

The COUNT function returns the number of rows that match a specific condition. It's commonly used with GROUP BY to get counts per group.

SELECT customer_id, COUNT(order_id) 
FROM orders 
GROUP BY customer_id;

This query counts the number of orders for each customer.

Order ID	Customer ID	Order Total
101	1	$1,500
102	2	$2,000
103	1	$1,000
104	2	$3,500

3. SUM Function

The SUM function calculates the total sum of a numeric column. It's often used to calculate total sales, quantities, etc.

SELECT customer_id, SUM(order_total) 
FROM orders 
GROUP BY customer_id;

This query calculates the total order amount for each customer.

Customer ID	Total Orders ($)
1	$2,500
2	$5,500