Multiple row Functions

SUM / AVG / MAX / MIN

• SELECT SUM/AVG(…) FROM … WHERE …
• AVG omits NULL value

COUNT

• SELECT COUNT(…) FROM … WHERE …
• COUNT omits NULL value
• COUNT(*) does not omit NULL value

Order of evaluation

• WHERE -> GROUP BY -> HAVING
• Filter results using HAVING after GROUP BY of WHERE results
• GROUP BY MUST BE:
  • USED WITH A GROUP FUNCTION ABOVE, OR WILL RAISE AN ERROR
  • The thing after SELECT that is not a group function can only be the col being grouped by

Subqueries

• < ANY (SELECT col FROM table …) = < (SELECT MAX(col) FROM table …)
• > ALL (SELECT col FROM table …) = > (SELECT MAX(col) FROM table …)
• P23:
  • A: get the max item number in an order
  • B: get what order has that max item number
  • C: get what customer made the order that has max item number