SQL GROUP BY Statement

The GROUP BY statement in SQL is used to group together rows that have the same values in one or more columns.

This allows for aggregate functions (such as COUNT, SUM, AVG, etc.) to be performed on the grouped data, providing a summary of the data.

Syntax

The basic syntax of the GROUP BY statement is as follows:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE some_column = some_value
GROUP BY column1, column2;
  • SELECT : specifies the columns that you want to retrieve.
  • FROM : specifies the table that you want to retrieve the data from.
  • WHERE : specifies a condition that must be met for the row to be included in the result set.
  • GROUP BY : specifies the columns that you want to group the rows by.
  • aggregate_function : specify the aggregate function like SUM, COUNT, AVG etc.

Examples

Let's look at a few examples of how the GROUP BY statement can be used:

Example 1: Grouping data by a single column

Suppose we have a table called "orders" that contains information about customer orders.

We want to know how many orders each customer has placed.

We can use the GROUP BY statement to group the data by the "customer_id" column and use the COUNT function to count the number of orders for each customer:

SELECT customer_id, COUNT(*) as 'Number of Orders'
FROM orders
GROUP BY customer_id;

Example 2: Grouping data by multiple columns

Suppose we have a table called "sales" that contains information about sales.

We want to know the total sales for each product in each category.

We can use the GROUP BY statement to group the data by the "product_name" and "category" columns and use the SUM function to calculate the total sales for each group:

SELECT category, product_name, SUM(sales_amount) as 'Total Sales'
FROM sales
GROUP BY category, product_name;

Example 3: Using a HAVING clause

The HAVING clause is used to filter the groups based on the aggregate function applied to the groups.

It is used in conjunction with the GROUP BY clause.

SELECT category, SUM(sales_amount) as 'Total Sales'
FROM sales
GROUP BY category
HAVING SUM(sales_amount) > 10000;

In this example, we are only displaying the rows where the sum of sales_amount for a category is greater than 10000.

Conclusion

In this guide, we've discussed the basics of the SQL GROUP BY statement, including its syntax and examples of how it can be used.

The GROUP BY statement is a powerful tool that allows you to group data together and perform aggregate functions on that data, making it easier to analyze and understand your data.

SQL Basics