CC SQL 13-grouping Data

Card Set Information

Author:
dau108
ID:
146335
Filename:
CC SQL 13-grouping Data
Updated:
2012-04-08 05:41:50
Tags:
CC SQL 13 grouping Data
Folders:

Description:
CC SQL 13-grouping Data
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  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

What would you like to do?

Home > Flashcards > Print Preview