Day 3: Functions & Grouping

Aggregate Functions	Scalar Functions (Math & String)	Grouping Clauses
`COUNT()` : Number of rows `SUM()` : Total sum `AVG()` : Average value `MAX()` : Highest value `MIN()` : Lowest value	Math: `ABS()`, `CEILING()`, `FLOOR()` String: `LEN()`, `UPPER()`, `LOWER()`, `LTRIM()`, `RTRIM()`	`GROUP BY` : Groups rows that have the same values `HAVING` : Filters data after it has been grouped

1. Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single summarizing value. They are incredibly useful for reporting and data analysis.

-- Count total number of employees
SELECT COUNT(EmpID) AS TotalEmployees FROM Employees;

-- Find the highest and lowest salaries
SELECT MAX(Salary) AS HighestPay, MIN(Salary) AS LowestPay FROM Employees;

-- Calculate total and average sales
SELECT SUM(OrderTotal) AS TotalRevenue, AVG(OrderTotal) AS AverageOrder FROM Orders;

2. Mathematical Functions

SQL Server provides built-in functions to perform math operations directly in your queries.

ABS(number): Returns the absolute (positive) value of a number.
CEILING(number): Rounds a number UP to the nearest integer.
FLOOR(number): Rounds a number DOWN to the nearest integer.

-- Examples:
SELECT ABS(-45.5);      -- Returns: 45.5
SELECT CEILING(12.3);   -- Returns: 13
SELECT FLOOR(12.8);     -- Returns: 12

3. String Functions

String functions allow you to manipulate text data. This is essential for cleaning up messy data entered by users.

LEN(string): Returns the number of characters in a string.
UPPER(string) / LOWER(string): Converts text to all caps or all lowercase.
LTRIM(string) / RTRIM(string): Removes extra spaces from the Left or Right side of a string.

-- Clean up user inputs and format names
SELECT 
    UPPER(FirstName) AS NameUpper, 
    LEN(LastName) AS NameLength,
    LTRIM(RTRIM(Email)) AS CleanEmail -- Removes spaces from BOTH sides
FROM Users;

4. Data Grouping (GROUP BY)

The GROUP BY statement groups rows that have the same values into summary rows. It is almost always used in conjunction with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

-- Find the total number of employees in EACH department
SELECT Department, COUNT(EmpID) AS NumberOfEmployees
FROM Employees
GROUP BY Department;

Golden Rule: If you select a column (like 'Department') AND an aggregate function (like 'COUNT') at the same time, you must put the non-aggregated column in the GROUP BY clause.

5. Filtering Groups (HAVING) vs. WHERE

This is a critical concept and one of the most common SQL interview questions. Both WHERE and HAVING are used to filter data, but they act at completely different stages of a query and have different rules.

The Core Differences

Feature	WHERE Clause	HAVING Clause
Execution Stage	Filters individual rows before the data is grouped.	Filters grouped records after the data has been grouped.
Aggregate Functions	Cannot use aggregate functions (e.g., `SUM`, `COUNT`).	Can (and usually does) use aggregate functions.
Command Usage	Can be used with `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.	Can only be used with `SELECT` statements.

Example: Combining Both in One Query

Let's say we want to find Departments that have an average salary greater than 60,000, but we only want to look at full-time employees.

SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
WHERE Status = 'Full-Time'        -- 1. WHERE filters individual rows FIRST
GROUP BY Department               -- 2. Then GROUP BY organizes the remaining rows
HAVING AVG(Salary) > 60000;       -- 3. Finally, HAVING filters the aggregated groups