How to avoid error "aggregate functions are not allowed in WHERE"

sql

aggregate-functions

where-clause

having-clause

byAlex Kataev·Feb 14, 2025

To dodge the "aggregate functions are not allowed in WHERE" riddle, modify the keyword with HAVING when tinkering with aggregates. The strength of HAVING steps forward after groups are assembled, contrasting with WHERE.

An express example with departments and their accumulated salaries:

SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary) > 100000;

This snippet rounds up employees under DepartmentID, sums up their wages, and then uses HAVING to present only those departments surpassing the given salary benchmark.

Catching the essence of WHERE and HAVING

Exploring aggregate functions brings to light a difference between individual data treatment and grouped inspections. The WHERE clause is a specialist, operating on single records. So, it's incompatible with the batch operators—COUNT(), SUM(), AVG()—that work on an entire dataset.

In contrast, the HAVING clause is a group therapist, drafted into service after GROUP BY organizes raw records into defined groups. Picture it as a bouncer selecting which VIP groupies get into the exclusive result set.

Showing HAVING in action

Imagine you need to showcase categories with a minimum product limit. Here's how WHERE can lead you down a rabbit hole:

-- Hmm. This is going to...Error. City.
SELECT CategoryID
FROM Products
WHERE COUNT(ProductID) > 10;

To flip the script and get back on track, replace WHERE with HAVING:

-- Like magic, but with code.
SELECT CategoryID
FROM Products
GROUP BY CategoryID
HAVING COUNT(ProductID) > 10;

HAVING in subquery scenarios

In more complex queries, usually with subqueries, remember that HAVING plays a vital role. The subquery filtering with aggregates, before the main query, gets a boost from HAVING:

-- Subqueries can be tricky, but HAVING always has our back.
SELECT p.*
FROM Products p
WHERE p.CategoryID IN (
  SELECT CategoryID
  FROM Products
  GROUP BY CategoryID
  HAVING COUNT(ProductID) > 10
);

Common traps and troubleshooting

Co-relating issues pop up when grouping implications are misunderstood. If your HAVING seems impotent, revisit:

Grouping logic: Are the appropriate fields clumped in the GROUP BY?
Aggregate accuracy: Does the aggregate function in HAVING align with the desired calculation? Precision is key.
Subquery structure: While dealing with subqueries, HAVING needs a proper setup to return the correct dataset for the outer query.

HAVING: Beyond the basics

Selective aggregation with HAVING

Harness the power of HAVING to work smart, not hard. Use it to zoom in on your groups of interest:

-- The smart way of working
SELECT EmployeeID, COUNT(SaleID) As SalesCount
FROM Sales
GROUP BY EmployeeID
HAVING COUNT(SaleID) > (SELECT AVG(SalesCount) FROM Sales);

Layering aggregates with HAVING

You can enrich data powerfully by layering HAVING clauses and using multiple aggregate conditions:

-- Data enrichment with layering
SELECT CategoryID
FROM Products
GROUP BY CategoryID
HAVING COUNT(ProductID) > 10 AND AVG(Price) > 15;

HAVING and JOIN: A powerful union

Combine HAVING with JOIN operations to fine-tune filtering after aggregation:

-- When HAVING meets JOIN, magic happens
SELECT e.DepartmentID, COUNT(distinct e.EmployeeID) AS NumEmployees
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY e.DepartmentID
HAVING COUNT(distinct e.EmployeeID) > 5;