The GROUP BY clause in a SELECT statement is used to group rows in a table based on one or more columns. This allows you to perform aggregate functions, such as SUM, COUNT, and AVG, on the grouped data. The syntax for using the GROUP BY clause is as follows:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
For example, if you want to find the total sales for each product category, you would use the following query:
SELECT category, SUM(sales)
FROM products
GROUP BY category;
The HAVING clause is used in conjunction with the GROUP BY clause to filter the groups based on a condition. The HAVING clause is used to filter the groups based on the result of an aggregate function. The syntax for using the HAVING clause is as follows:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING aggregate_function(column2) operator value;
For example, if you want to find the total sales for each product category, but only for categories with more than $10,000 in sales, you would use the following query:
SELECT category, SUM(sales)
FROM products
GROUP BY category
HAVING SUM(sales) > 10000;
It’s important to note that the HAVING clause is used after the GROUP BY clause and it can only be used with aggregate functions, you can’t use HAVING clause without GROUP BY.