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.
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.
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.
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.