SQL GROUP BY Tutorial 📝

Welcome to our comprehensive guide on SQL GROUP BY! In this tutorial, we'll explore how to organize data using the GROUP BY statement, making it easy to perform calculations on groups of records within a database. Let's get started!

What is SQL GROUP BY? 🎯

The SQL GROUP BY statement is used to group rows that have the same values in specified columns. This allows us to perform calculations such as SUM, AVG, MIN, MAX, and COUNT on each group.

Why use SQL GROUP BY? 💡

Imagine you have a sales database with columns for product_id, sales_date, and sales_amount. You want to find out the total sales for each product across different months. This is where GROUP BY comes in handy!

How to use SQL GROUP BY 📝

Here's a simple example:

sql

SELECT product_id, SUM(sales_amount) as total_sales
FROM sales
GROUP BY product_id;

📝 Note: Replace sales, product_id, and sales_amount with your actual table name and column names.

GROUP BY Syntax 📝

The basic syntax for GROUP BY is:

sql

SELECT column1, column2, ..., columnN, aggregate_function(columnX)
FROM table_name
GROUP BY column1, column2, ..., columnN
ORDER BY columnX;

column1, column2, ..., columnN: Columns to display for each group.
aggregate_function(columnX): Function to perform calculations (e.g., SUM, AVG, MIN, MAX, COUNT).
table_name: Name of the table containing the data.
columnX: Column for which you want to perform calculations.

Real-world example 💡

Let's consider a blog site with articles, authors, and categories. We want to find out the total number of articles for each category.

sql

SELECT category, COUNT(article_id) as total_articles
FROM articles
JOIN categories ON articles.category_id = categories.category_id
GROUP BY category;

📝 Note: In this example, we're using a JOIN statement to combine the articles and categories tables.

Advanced GROUP BY examples 💡

GROUP BY multiple columns

sql

SELECT category, author, COUNT(article_id) as total_articles
FROM articles
JOIN categories ON articles.category_id = categories.category_id
JOIN authors ON articles.author_id = authors.author_id
GROUP BY category, author;

Using HAVING clause

sql

SELECT category, COUNT(article_id) as total_articles
FROM articles
JOIN categories ON articles.category_id = categories.category_id
WHERE views > 1000
GROUP BY category
HAVING total_articles > 5;

📝 Note: The HAVING clause is used to filter groups.

Quiz 🎯

Happy coding! 💻💪