Database Reference
In-Depth Information
So how do you filter rows? Look at the following example:
▼
Input
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
▼
Output
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
+---------+--------+
▼
Analysis
The first three lines of this
SELECT
statement are similar to the statements seen
previously. The final line adds a
HAVING
clause that filters on those groups with
a
COUNT(*) >= 2
—two or more orders.
As you can see, a
WHERE
clause does not work here because the filtering is based
on the group aggregate value, not on the values of specific rows.
Note
The Difference Between
HAVING
and
WHERE
Here's another way to look at it:
WHERE
filters before data is grouped, and
HAVING
filters after data is grouped. This is an impor-
tant distinction; rows that are eliminated by a
WHERE
clause are not included in the
group. This could change the calculated values, which in turn could affect which groups
are filtered based on the use of those values in the
HAVING
clause.
So is there ever a need to use both
WHERE
and
HAVING
clauses in one statement?
Actually, yes, there is. Suppose you want to further filter the previous state-
ment so it returns any customers who placed two or more orders in the past 12
months. To do that, you can add a
WHERE
clause that filters out just the orders
placed in the past 12 months. You then add a
HAVING
clause to filter just the
groups with two or more rows in them.
To better demonstrate this, look at the following example that lists all vendors
who have
2
or more products priced at
10
or more:
