GROUP BY

The SQL GROUP BY Statement

The SQL GROUP BY statement is used to group rows that have the same values into summary rows, typically to apply aggregate functions like COUNT, SUM, AVG, MAX, or MIN to each group. It is often used in combination with aggregate functions to perform calculations on each group separately. The basic syntax of the GROUP BY statement is as follows:

SELECT column1, column2, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

• column1, column2, ...: Columns you want to include in the result set.
• aggregate_function(column_name): Aggregate functions like COUNT, SUM, AVG, MAX, or MIN applied to specific columns.
• table_name: Name of the table you're querying.
• condition: Optional. Conditions to filter rows before grouping.
• GROUP BY column1, column2, ...: Specifies the columns by which to group the result set.

For example, let's say you have a table called orders with columns customer_id and total_amount. If you want to calculate the total amount spent by each customer, you can use the GROUP BY statement like this:

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

In this example:

• We select the customer_id column and calculate the sum of total_amount for each customer.
• The GROUP BY customer_id clause ensures that the result set is grouped by the customer_id column.

The result will contain each customer_id along with the corresponding total amount spent by that customer.

Remember that when you use a GROUP BY statement, all selected columns must either be listed in the GROUP BY clause or be arguments of aggregate functions. Otherwise, most SQL databases will throw an error.