4.5 GROUP BY and HAVING clauses

 The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like "total sales by region" or "average salary by department." The HAVING clause filters groups based on a condition.

4.5.1 GROUP BY Clause

The syntax for the GROUP BY clause is as follows:

SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY column1, column2;

Let's consider a table named Orders:

OrderID	CustomerID	Product	Quantity	Price
1	101	'Apples'	10	1.00
2	102	'Bananas'	5	0.75
3	101	'Oranges'	8	1.25
4	103	'Apples'	12	1.00
5	102	'Grapes'	6	2.00

Example:

SELECT CustomerID, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY CustomerID;

CustomerID	TotalQuantity
101	18
102	11
103	12

4.5.2 HAVING Clause

The HAVING clause is used to filter groups based on a condition. It is often used with the GROUP BY clause.

The syntax is as follows:

SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1
HAVING condition;

Example:

SELECT CustomerID, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY CustomerID
HAVING SUM(Quantity) > 15;

CustomerID	TotalQuantity
101	18

In this example, only the group where the total quantity is greater than 15 is included.

---

Feel free to run these SQL queries in your preferred SQL Server environment to see the results. This tutorial provides a basic understanding of how to use the GROUP BY and HAVING clauses in SQL.