CC SQL 13-grouping Data

• SELECT vend_id, COUNT(*) AS num_prods
• FROM products
• GROUP BY vend_id;

• SELECT cust_id, COUNT(*) AS orders
• FROM orders
• GROUP BY cust_id
• HAVING COUNT(*) >= 2;

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 important 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.

• example:
• To better demonstrate this, look at the following example that lists all vendors who have 2 or more products priced at 10 or more:

• InputSELECT vend_id, COUNT(*) AS num_prods
• FROM products
• WHERE prod_price >= 10
• GROUP BY vend_id
• HAVING COUNT(*) >= 2;

• SELECT order_num, SUM(quantity*item_price) AS ordertotal
• FROM orderitems
• GROUP BY order_num
• HAVING SUM(quantity*item_price) >= 50
• ORDER BY ordertotal;