SQL GROUP BY Tutorial

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into aggregated data. It is often used with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group of rows.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example

Suppose you have a table named Orders with the following data:

OrderID	CustomerID	Amount
1	1	100
2	1	200
3	2	150
4	2	300

To find the total amount spent by each customer, you can use the following query:

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID;

Result:

CustomerID	TotalAmount
1	300
2	450

Key Points

• GROUP BY is used to group rows based on one or more columns.
• It is often used with aggregate functions to summarize data.
• Columns in the SELECT clause must either be in the GROUP BY clause or be used with an aggregate function.

Common Mistakes

• Forgetting to include non-aggregated columns in the GROUP BY clause.
• Using GROUP BY without an aggregate function, which may not produce the desired results.

Conclusion

The GROUP BY clause is a powerful tool in SQL for summarizing and analyzing data. By understanding how to use it effectively, you can gain valuable insights from your datasets.