To select rows with a specific condition using HAVING, I use it when I’m dealing with aggregate functions like SUM, AVG, COUNT, etc., after grouping data with GROUP BY. Unlike WHERE, which filters individual rows before grouping, HAVING filters after the aggregation is done.
For example, if I want to find departments where the total salary paid is more than ₹1,00,000, I’d write:
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000;
Here, GROUP BY first groups employees department-wise, then HAVING filters only those departments whose total salary exceeds ₹1,00,000.
In one of my payroll reports, I used a similar query to identify cost-heavy departments. We grouped by department and applied HAVING on SUM(Salary) to highlight only those exceeding the allocated budget.
A challenge I faced initially was confusing HAVING with WHERE. For example, WHERE can’t be used with aggregates like SUM() or AVG(). So, if I need to filter individual records (say, active employees), I use WHERE, and for aggregated conditions, I use HAVING — like this:
SELECT Department, COUNT(*) AS EmpCount
FROM Employees
WHERE Status = 'Active'
GROUP BY Department
HAVING COUNT(*) > 10;
That gives me only departments with more than 10 active employees.
So in short, I use HAVING when I need to apply a condition on grouped or aggregated results, making it a powerful tool for analytics and reporting queries.