SQL Grouping & Aggregation

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value.

Common Aggregate Functions:

Sample Data for Examples

Employees Table:

sql

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

INSERT INTO Employees VALUES
(1, 'John Smith', 'IT', 75000, '2020-01-15'),
(2, 'Jane Doe', 'HR', 65000, '2019-03-20'),
(3, 'Bob Johnson', 'IT', 80000, '2018-07-10'),
(4, 'Alice Brown', 'Finance', 70000, '2021-05-30'),
(5, 'Mike Davis', 'HR', 60000, '2020-11-22'),
(6, 'Sarah Wilson', 'IT', 85000, '2022-02-14'),
(7, 'Tom Miller', 'Finance', 72000, '2017-09-05'),
(8, 'Lisa Taylor', 'Marketing', 68000, '2021-08-12');

Sales Table:

sql

CREATE TABLE Sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(50),
    employee_id INT
);

INSERT INTO Sales VALUES
(1, 'Laptop', '2024-01-15', 1200.00, 'North', 1),
(2, 'Smartphone', '2024-01-20', 800.00, 'South', 3),
(3, 'Tablet', '2024-01-25', 600.00, 'North', 1),
(4, 'Laptop', '2024-02-05', 1200.00, 'East', 6),
(5, 'Monitor', '2024-02-10', 300.00, 'West', 3),
(6, 'Smartphone', '2024-02-15', 800.00, 'North', 1),
(7, 'Tablet', '2024-03-01', 600.00, 'South', 6),
(8, 'Laptop', '2024-03-10', 1200.00, 'East', 3),
(9, 'Monitor', '2024-03-20', 300.00, 'West', 1),
(10, 'Smartphone', '2024-03-25', 800.00, 'North', 6);

Basic Aggregate Functions

COUNT()

sql

-- Count all employees
SELECT COUNT(*) AS total_employees FROM Employees;

-- Count employees in IT department
SELECT COUNT(*) AS it_employees 
FROM Employees 
WHERE department = 'IT';

-- Count distinct departments
SELECT COUNT(DISTINCT department) AS unique_departments 
FROM Employees;

SUM() and AVG()

sql

-- Total salary expenditure
SELECT SUM(salary) AS total_salary_cost FROM Employees;

-- Average salary
SELECT AVG(salary) AS average_salary FROM Employees;

-- Average salary in IT department
SELECT AVG(salary) AS avg_it_salary 
FROM Employees 
WHERE department = 'IT';

MAX() and MIN()

sql

-- Highest and lowest salary
SELECT MAX(salary) AS highest_salary, 
       MIN(salary) AS lowest_salary 
FROM Employees;

-- Highest salary in each department
SELECT department, MAX(salary) AS highest_salary 
FROM Employees 
GROUP BY department;

GROUP BY Clause

The GROUP BY clause groups rows that have the same values into summary rows.

Syntax:

sql

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

GROUP BY Examples

Count employees per department:

sql

SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department;

Result:

text

| department | employee_count |
|------------|----------------|
| IT         | 3              |
| HR         | 2              |
| Finance    | 2              |
| Marketing  | 1              |

Average salary by department:

sql

SELECT department, 
       AVG(salary) AS avg_salary,
       ROUND(AVG(salary), 2) AS avg_salary_rounded
FROM Employees
GROUP BY department
ORDER BY avg_salary DESC;

Total sales by product:

sql

SELECT product_name, 
       SUM(amount) AS total_sales,
       COUNT(*) AS number_of_sales
FROM Sales
GROUP BY product_name
ORDER BY total_sales DESC;

HAVING Clause

The HAVING clause filters groups based on aggregate conditions (similar to WHERE but for groups).

Syntax:

sql

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

HAVING vs WHERE:

• WHERE filters rows before grouping

• HAVING filters groups after grouping

HAVING Examples

Departments with more than 2 employees:

sql

SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department
HAVING COUNT(*) > 2;

Products with total sales over $2000:

sql

SELECT product_name, SUM(amount) AS total_sales
FROM Sales
GROUP BY product_name
HAVING SUM(amount) > 2000;

Departments with average salary above $70,000:

sql

SELECT department, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department
HAVING AVG(salary) > 70000;

Multiple Column Grouping

You can group by multiple columns to get more detailed breakdowns.

sql

-- Sales by product and region
SELECT product_name, region, 
       SUM(amount) AS total_sales,
       COUNT(*) AS transaction_count
FROM Sales
GROUP BY product_name, region
ORDER BY product_name, total_sales DESC;

Practical Reporting Examples

Exercise 1: Total Sales by Month

sql

-- Monthly sales summary
SELECT 
    EXTRACT(YEAR FROM sale_date) AS sale_year,
    EXTRACT(MONTH FROM sale_date) AS sale_month,
    SUM(amount) AS monthly_sales,
    COUNT(*) AS total_transactions,
    AVG(amount) AS avg_transaction
FROM Sales
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY sale_year, sale_month;

Alternative using date formatting:

sql

-- MySQL
SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS sales_month,
    SUM(amount) AS monthly_sales
FROM Sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY sales_month;

-- PostgreSQL
SELECT 
    TO_CHAR(sale_date, 'YYYY-MM') AS sales_month,
    SUM(amount) AS monthly_sales
FROM Sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY sales_month;

Exercise 2: Count of Employees per Department

sql

-- Basic employee count by department
SELECT 
    department,
    COUNT(*) AS employee_count,
    ROUND(AVG(salary), 2) AS average_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM Employees
GROUP BY department
ORDER BY employee_count DESC;

Exercise 3: Sales Performance by Employee

sql

-- Employee sales performance
SELECT 
    e.name,
    e.department,
    COUNT(s.sale_id) AS total_sales,
    SUM(s.amount) AS total_revenue,
    ROUND(AVG(s.amount), 2) AS avg_sale_amount
FROM Employees e
JOIN Sales s ON e.employee_id = s.employee_id
GROUP BY e.employee_id, e.name, e.department
HAVING COUNT(s.sale_id) >= 2  -- Employees with at least 2 sales
ORDER BY total_revenue DESC;

Exercise 4: Regional Sales Analysis

sql

-- Regional sales breakdown
SELECT 
    region,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_revenue,
    ROUND(AVG(amount), 2) AS avg_transaction,
    MIN(amount) AS smallest_sale,
    MAX(amount) AS largest_sale
FROM Sales
GROUP BY region
HAVING SUM(amount) > 1000  -- Regions with significant sales
ORDER BY total_revenue DESC;

Advanced Aggregation Examples

Using Multiple Aggregate Functions

sql

-- Comprehensive department summary
SELECT 
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    ROUND((MAX(salary) - MIN(salary)), 2) AS salary_range
FROM Employees
GROUP BY department
ORDER BY avg_salary DESC;

Conditional Aggregation

sql

-- Count employees hired by year
SELECT 
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    COUNT(*) AS employees_hired
FROM Employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;

-- Sales by product with percentage of total
SELECT 
    product_name,
    SUM(amount) AS product_sales,
    ROUND((SUM(amount) / (SELECT SUM(amount) FROM Sales)) * 100, 2) AS percent_of_total
FROM Sales
GROUP BY product_name
ORDER BY product_sales DESC;

Common Mistakes and Tips

1. Include all non-aggregated columns in GROUP BY

   sql

   -- Wrong (department not in GROUP BY)
   SELECT department, name, AVG(salary) 
   FROM Employees;
   
   -- Correct
   SELECT department, AVG(salary) 
   FROM Employees 
   GROUP BY department;

    

2. Use HAVING for aggregate conditions, WHERE for row conditions

   sql

   -- Employees in IT department with average salary > 70000
   SELECT department, AVG(salary) 
   FROM Employees 
   WHERE department = 'IT' 
   GROUP BY department 
   HAVING AVG(salary) > 70000;

    

3. Combine GROUP BY with ORDER BY for better readability

   sql

   SELECT department, COUNT(*) AS employee_count
   FROM Employees
   GROUP BY department
   ORDER BY employee_count DESC;