CC SQL 13-grouping Data

  1. creating groups
    • SELECT vend_id, COUNT(*) AS num_prods
    • FROM products
    • GROUP BY vend_id;
  2. filtering groups:

    WHERE filters rows and HAVING filters groups
    • 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;
  3. grouping and sorting
    • SELECT order_num, SUM(quantity*item_price) AS ordertotal
    • FROM orderitems
    • GROUP BY order_num
    • HAVING SUM(quantity*item_price) >= 50
    • ORDER BY ordertotal;
  4. clause ordering
    Image Upload 2
Card Set
CC SQL 13-grouping Data
CC SQL 13-grouping Data