< Day Day Up > Filtering Groups In addition to being able to group data using GROUP BY, SQL also allows you to filter which groups to include and which to exclude. For example, you might want a list of all customers who have made at least two orders. To obtain this data you must filter based on the complete group, not on individual rows.You've already seen the WHERE clause in action (that was introduced back in Lesson 4, "Filtering Data." But WHERE does not work here because WHERE filters specific rows, not groups. As a matter of fact, WHERE has no idea what a group is.So what do you use instead of WHERE? SQL provides yet another clause for this purpose: the HAVING clause. HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can also be used with HAVING. The only difference is that WHERE filters rows and HAVING filters groups. HAVING Supports All of WHERE's Operators In Lesson 4 and Lesson 5, "Advanced Data Filtering," you learned about WHERE clause conditions (including wildcard conditions and clauses with multiple operators). All the techniques and options that you learned about WHERE can be applied to HAVING. The syntax is identical; just the keyword is different.
So how do you filter rows? Look at the following example:SELECT cust_id, COUNT(*) AS ordersFROM OrdersGROUP BY cust_idHAVING COUNT(*) >= 2; cust_id orders---------- -----------1000000001 2 The first three lines of this SELECT statement are similar to the statements seen above. 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. The difference between HAVING and WHERE Here's another way to look it: WHERE filters before data is grouped, and HAVING filters after data is grouped. This is an important distinction; rows that are eliminated by a WHERE clause will not be 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 above statement so that 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 two or more products priced at 4 or more:SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsWHERE prod_price >= 4GROUP BY vend_idHAVING COUNT(*) >= 2; vend_id num_prods---------- -----------BRS01 3FNG01 2 This statement warrants an explanation. The first line is a basic SELECT using an aggregate function—much like the examples thus far. The WHERE clause filters all rows with a prod_price of at least 4. Data is then grouped by vend_id, and then a HAVING clause filters just those groups with a count of 2 or more. Without the WHERE clause an extra row would have been retrieved (vendor DLL01 who sells four products all priced under 4) as seen here:
SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsGROUP BY vend_idHAVING COUNT(*) >= 2; vend_id num_prods---------- -----------BRS01 3DLL01 4FNG01 2 Using HAVING and WHERE HAVING is so similar to WHERE that most DBMSs treat them as the same thing if no GROUP BY is specified. Nevertheless, you should make that distinction yourself. Use HAVING only in conjunction with GROUP BY clauses. Use WHERE for standard row-level filtering.
In addition to being able to group data using GROUP BY, SQL also allows you to filter which groups to include and which to exclude. For example, you might want a list of all customers who have made at least two orders. To obtain this data you must filter based on the complete group, not on individual rows.You've already seen the WHERE clause in action (that was introduced back in Lesson 4, "Filtering Data." But WHERE does not work here because WHERE filters specific rows, not groups. As a matter of fact, WHERE has no idea what a group is.So what do you use instead of WHERE? SQL provides yet another clause for this purpose: the HAVING clause. HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can also be used with HAVING. The only difference is that WHERE filters rows and HAVING filters groups. HAVING Supports All of WHERE's Operators In Lesson 4 and Lesson 5, "Advanced Data Filtering," you learned about WHERE clause conditions (including wildcard conditions and clauses with multiple operators). All the techniques and options that you learned about WHERE can be applied to HAVING. The syntax is identical; just the keyword is different.
So how do you filter rows? Look at the following example:SELECT cust_id, COUNT(*) AS ordersFROM OrdersGROUP BY cust_idHAVING COUNT(*) >= 2;
cust_id orders---------- -----------1000000001 2
The first three lines of this SELECT statement are similar to the statements seen above. 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. The difference between HAVING and WHERE Here's another way to look it: WHERE filters before data is grouped, and HAVING filters after data is grouped. This is an important distinction; rows that are eliminated by a WHERE clause will not be 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 above statement so that 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 two or more products priced at 4 or more:SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsWHERE prod_price >= 4GROUP BY vend_idHAVING COUNT(*) >= 2;
vend_id num_prods---------- -----------BRS01 3FNG01 2
This statement warrants an explanation. The first line is a basic SELECT using an aggregate function—much like the examples thus far. The WHERE clause filters all rows with a prod_price of at least 4. Data is then grouped by vend_id, and then a HAVING clause filters just those groups with a count of 2 or more. Without the WHERE clause an extra row would have been retrieved (vendor DLL01 who sells four products all priced under 4) as seen here:
SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsGROUP BY vend_idHAVING COUNT(*) >= 2;
vend_id num_prods---------- -----------BRS01 3DLL01 4FNG01 2
Using HAVING and WHERE HAVING is so similar to WHERE that most DBMSs treat them as the same thing if no GROUP BY is specified. Nevertheless, you should make that distinction yourself. Use HAVING only in conjunction with GROUP BY clauses. Use WHERE for standard row-level filtering.
< Day Day Up >