How do I use GROUP BY with aggregate functions?

GROUP BY in SQL is used to group rows that have the same values in specified columns into aggregated results. When combined with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN(), it allows you to perform operations on groups of data rather than individual rows.

1. Basic Structure of GROUP BY

The typical SQL query with GROUP BY looks like this:

sqlCopy codeSELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2;

• column1 and column2: These are the columns by which the results are grouped.
• AGGREGATE_FUNCTION: An aggregate function like SUM(), AVG(), or COUNT() to perform calculations on grouped data.

2. Example: Counting Items per Category

Suppose you have a table called products:

To count the number of products in each category, you would use:

sqlCopy codeSELECT category, COUNT(*)
FROM products
GROUP BY category;

Output:

3. Using Multiple Aggregate Functions

You can use multiple aggregate functions in the same query. For example, to calculate the total price and the average price per category:

sqlCopy codeSELECT category, SUM(price) AS total_price, AVG(price) AS average_price
FROM products
GROUP BY category;

Output:

4. Filtering Groups with HAVING

Sometimes, you may want to filter the grouped results. This is done using the HAVING clause (instead of WHERE, which is used before aggregation). For example, to show only categories where the total price exceeds 100:

sqlCopy codeSELECT category, SUM(price) AS total_price
FROM products
GROUP BY category
HAVING SUM(price) > 100;

Output:

Conclusion

GROUP BY combined with aggregate functions allows you to perform complex calculations on groups of data in SQL. It’s particularly useful for generating reports or summaries from large datasets, like counting items, calculating totals, and computing averages. You can enhance its power with multiple functions and filters using HAVING for more refined results.