SQL分组和聚合（Grouping and Aggregates）

这章应该是难点，也是成为SQL高手的必经之路。

注意有GROUP 语句时，WHERE和HAVING的场合。

前者用于检索前的条件过滤 。

后者用于检索出来结果之后的条件过滤。

==========

SELECT open_emp_id , COUNT(*) AS how_many FROM account GROUP BY open_emp_id HAVING open_emp_id > 10;

SELECT product_cd, MAX(avail_balance) max_balance, MIN(avail_balance) min_balance, AVG(avail_balance) avg_balance, SUM(avail_balance) tot_balance, COUNT(*) num_accounts FROM account GROUP BY product_cd ;

SELECT COUNT(DISTINCT open_emp_id);

SELECT MAX(pending_balance - avail_balance) max_uncleared FROM account;

SELECT COUNT(*) num_rows, COUNT(val) num_vals, SUM(val) total, MAX(val) max_val, AVG(val) avg_val FROM number_tbl;

SELECT product_cd, open_branch_id, SUM(avail_balance) total_balance FROM account GROUP BY product_cd, open_branch_id;

SELECT EXTRACT(YEAR FROM start_date) year, COUNT(*) how_many FROM employee GROUP BY EXTRACT(YEAR FROM start_date);

SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance FROM account GROUP BY product_cd, open_branch_id WITH ROLLUP;

SELECT product_cd, SUM(avail_balance) prod_balance FROM account WHERE status = 'ACTIVE' GROUP BY product_cd HAVING MIN(avail_balance) >= 1000 AND MAX(avail_balance) <= 10000;