Knowledge Base

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== Conditions on aggregates in SQL ======
You cannot apply a “where” condition to an aggregate in SQL. :-( For example, this does //not// work:

<code sql>
SELECT CustID, SUM(OrderAmt)
FROM Orders
GROUP BY CustID
WHERE SUM(OrderAmt) > 100  -- Doesn't work
</code>

There are a couple of ways to deal with this.

First, there’s the “having” clause, which specifically handles this requirement:

<code sql>
SELECT CustID, SUM(OrderAmt)
FROM Orders
GROUP BY CustID
HAVING SUM(OrderAmt) > 100
</code>

If the “having” clause doesn’t work for you, or you’d just prefer not to use it, you can also do some nested selecting and accomplish the same thing:

<code sql>
SELECT * FROM (
  SELECT CustID, SUM(OrderAmt) AS OrderSum
  FROM Orders
  GROUP BY CustID
) AS OrderGrp
WHERE OrderGrp.OrderSum > 100
</code>

{{tag>database}}