SQL GROUP BY Tutorial

Example Table

We will use the following table named sales for our examples:


    CREATE TABLE sales (
        sale_id INT PRIMARY KEY,
        product_name VARCHAR(50),
        sale_date DATE,
        amount DECIMAL(10, 2)
    );

    INSERT INTO sales (sale_id, product_name, sale_date, amount) VALUES
    (1, 'Product A', '2023-01-01', 100.00),
    (2, 'Product B', '2023-01-02', 150.00),
    (3, 'Product A', '2023-01-03', 200.00),
    (4, 'Product C', '2023-01-04', 250.00),
    (5, 'Product B', '2023-01-05', 300.00);

Sales Table

sale_id	product_name	sale_date	amount
1	Product A	2023-01-01	100.00
2	Product B	2023-01-02	150.00
3	Product A	2023-01-03	200.00
4	Product C	2023-01-04	250.00
5	Product B	2023-01-05	300.00

Using SQL GROUP BY

The SQL GROUP BY statement is used to arrange identical data into groups. This statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to perform operations on each group of data.

Example 1: Total Sales Amount by Product

To calculate the total sales amount for each product, use the following query:


    SELECT product_name, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product_name;

Result:

product_name	Product A
total_amount	300.00
	Product B
	450.00
	Product C
	250.00

Example 2: Average Sales Amount by Product

To calculate the average sales amount for each product, use the following query:


    SELECT product_name, AVG(amount) AS average_amount
    FROM sales
    GROUP BY product_name;