SQL code copied to buffer
Learn how to use the SQL GROUP BY clause to group and summarize your data efficiently. This lesson explains the basics of GROUP BY, multi-column grouping, and practical aggregation with real Sakila database examples. Mastering GROUP BY will help you create powerful reports and perform advanced data analysis in SQL. Ideal for beginners and those looking to improve their SQL reporting skills.
RU PT

Lesson 4.2: Grouping Data with GROUP BY in SQL

Grouping data is a key tool for analysis and summarization in SQL. The GROUP BY clause allows you to combine rows with the same values in specified columns and apply aggregation functions to each group. In this lesson, you'll learn how to use GROUP BY for reporting and data analysis with examples from the Sakila database.

Basics of Using GROUP BY

Syntax

SELECT column1, AGG_FUNCTION(column2)
FROM table
GROUP BY column1;

Example: Total payments per customer

SELECT customer_id, SUM(amount) AS total_paid
FROM payment
GROUP BY customer_id;

Result: Returns the total amount of payments for each customer.

Example: Number of payments per staff

SELECT staff_id, COUNT(*) AS payments_count
FROM payment
GROUP BY staff_id;

Result: Shows how many payments each staff member processed.

Example: Average payment by date

SELECT DATE(payment_date) AS pay_date, AVG(amount) AS avg_payment
FROM payment
GROUP BY pay_date;

Result: Returns the average payment amount for each date.

Using GROUP BY with Multiple Columns

You can group data by several columns at once for more detailed analysis.

Example: Total payments by staff and customer

SELECT staff_id, customer_id, SUM(amount) AS total_paid
FROM payment
GROUP BY staff_id, customer_id;

Result: Shows how much each staff member received from each customer.

Practical Usage

  1. Sales analysis by film category:
    SELECT c.name AS category, SUM(p.amount) AS total_sales
    FROM payment p
    JOIN rental r ON p.rental_id = r.rental_id
    JOIN inventory i ON r.inventory_id = i.inventory_id
    JOIN film f ON i.film_id = f.film_id
    JOIN film_category fc ON f.film_id = fc.film_id
    JOIN category c ON fc.category_id = c.category_id
    GROUP BY c.name;
    
  2. Number of customers by country:
    SELECT country, COUNT(*) AS customers_count
    FROM customer cu
    JOIN address a ON cu.address_id = a.address_id
    JOIN city ci ON a.city_id = ci.city_id
    JOIN country co ON ci.country_id = co.country_id
    GROUP BY country;
    

Key Takeaways from This Lesson

The GROUP BY clause lets you group data and apply aggregation functions to each group. It's a powerful tool for reporting and data analysis in SQL. Practice using GROUP BY with examples from the Sakila database to quickly get summary data and build analytical queries.