3. Summarizing Data: Aggregate Functions and Grouping in SQL

 SQL's Aggregate Functions are the tools you use to perform calculations on a set of rows and return a single summary value. These functions, especially when combined with GROUP BY and HAVING, are the backbone of data analytics.

---

The Five Core Aggregate Functions

These functions operate on the values of a column across the rows being queried:

Function	Purpose	Example Use
COUNT()	Returns the number of rows that match a specified criterion.	COUNT(*) (Total rows) or COUNT(CustomerID) (Total distinct customers)
SUM()	Calculates the total sum of a numeric column.	SUM(Price * Quantity) (Total revenue)
AVG()	Calculates the average (mean) value of a numeric column.	AVG(Price) (Average product price)
MIN()	Finds the minimum (lowest) value in a column.	MIN(Date) (Date of the earliest sale)
MAX()	Finds the maximum (highest) value in a column.	MAX(Price) (Highest product price)

Example: Find the total number of products and the average price.

SQL

LECT COUNT

SELECT COUNT(ProductID), AVG(Price) 

from Product;

---

Grouping Results: The GROUP BY Clause

The GROUP BY clause is used to divide the result set into groups based on one or more columns. The aggregate functions then calculate their summary value for each distinct group.

Example: Find the total number of customers in each city.

SELECT COUNT(CustomerID) 

from Customer

 GROUP BY City;

This query first groups all rows by their City value, then calculates the COUNT of customers within each resulting group.

---

Filtering Groups: The HAVING Clause

While the WHERE clause filters individual rows before aggregation, the HAVING clause filters the summary results of the GROUP BY clause after aggregation has occurred.

Example: Find the cities that have more than 50 customers.

SELECT COUNT(CustomerID) AS Customer_count 
FROM Customer 
GROUP BY City 
HAVING Customer_count >50;

COUNT(CustomerID) > 50;

 Clause Order

Remember the strict order of execution in a standard SQL query:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT (Including aggregate functions)

6. ORDER BY