Aggregate function in SQL WHERE-Clause

Advanced Use Cases

Pre-aggregation swing with WHERE and subqueries

Use a subquery in the WHERE clause to help filters out results before they even get to the party.

SELECT productName, unitPrice
FROM products
WHERE productId IN ( /* Think of this as the bouncer at the club */
  SELECT productId
  FROM orderDetails
  GROUP BY productId
  HAVING AVG(quantity) > 10
);


Pro Tip: Doing your filtering up front can dramatically reduce the amount of data your SQL engine has to process.

The HAVING club dance floor

HAVING clause is like the party-goer that arrives after everyone has grouped up and started dancing. HAVING moves after the aggregates have formed their own party.

SELECT department, MAX(salary) as biggestEarner
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000; /* "No high rollers allowed!" */


Remember: HAVING is here for conditions on data groups after the aggregation party has started.

Subqueries, Join the Party

JOINing the subquery club

When your WHERE clause and an aggregate function become best friends, they join the subquery team.

SELECT e.employeeId, e.name, deptAgg.totalSales
FROM employees e
JOIN (
    SELECT d.departmentId, SUM(s.amount) as totalSales
    FROM sales s
    INNER JOIN departments d ON s.departmentId = d.departmentId
    GROUP BY d.departmentId /* "Dancing in sync, everyone!" */
) deptAgg ON e.departmentId = deptAgg.departmentId
WHERE deptAgg.totalSales > 100000;


Key Takeaway: The subquery JOIN method masters the subquery dance floor, condensing filtering and aggregate computation into one limber move.

Don't crash the party! Avoiding errors

Inviting aggregates directly into a WHERE clause is a party faux pas. Your SQL engine will not appreciate the gesture and will give errors in return.

Remember: Be polite: aggregate functions are VIPs on the HAVING clause guest list or prefer the quiet subquery antechamber.

Advanced Aggregate Techniques

CASE when you need to switch things up

Mixing in a CASE statement allows you to bring conditional logic to your aggregate functions, creating dynamic data filters.

SELECT department, 
       SUM(CASE WHEN position = 'Manager' THEN salary END) as totalManagerSalary
FROM employees
GROUP BY department
HAVING totalManagerSalary > 100000; /* "Manager bonus party!" */


Tip: CASE statements within aggregates serve your flexible filtering needs with tailored elegance.

Special guests: Window functions

Beyond HAVING, modern SQL introduces window functions like ROW_NUMBER(), RANK(), or SUM() with OVER() clauses for a fresh perspective on aggregates.

SELECT department, salary, RANK() OVER (ORDER BY salary DESC) as salaryRank
FROM employees
WHERE salaryRank <= 10; /* "Making it rain for the top 10!" */


Note: But remember, window functions perform after the WHERE clause, hence subqueries are needed for pre-filtering application.