6. GROUP BY, HAVING and aggregates

SELECT d.DeptName,
       COUNT(*)          AS Headcount,
       AVG(e.Salary)     AS AvgSalary,
       MAX(e.Salary)     AS MaxSalary,
       SUM(e.Salary)     AS Payroll
FROM Employees e
JOIN Departments d ON d.DeptId = e.DeptId
WHERE e.IsActive = 1            -- filters rows BEFORE grouping
GROUP BY d.DeptName
HAVING COUNT(*) >= 3            -- filters groups AFTER grouping
ORDER BY Payroll DESC;

-- ROLLUP / CUBE / GROUPING SETS for subtotals
SELECT Region, Product, SUM(Amount) FROM Sales GROUP BY ROLLUP (Region, Product);

-- STRING_AGG (2017+)
SELECT DeptId, STRING_AGG(FullName, ', ') WITHIN GROUP (ORDER BY FullName) AS People FROM Employees GROUP BY DeptId;