Aggregation and Grouping

9.1 Aggregate Functions

SELECT COUNT(*), COUNT(commission_pct), COUNT(DISTINCT department_id),
       SUM(salary), AVG(salary), MIN(hire_date), MAX(salary),
       MEDIAN(salary), STDDEV(salary), VARIANCE(salary),
       APPROX_COUNT_DISTINCT(job_id),                 -- 12c
       APPROX_MEDIAN(salary), APPROX_PERCENTILE(0.9) WITHIN GROUP (ORDER BY salary)
FROM employees;

9.2 GROUP BY and HAVING

SELECT department_id, job_id, COUNT(*) cnt, ROUND(AVG(salary), 2) avg_sal
FROM employees
WHERE hire_date >= DATE '2015-01-01'
GROUP BY department_id, job_id
HAVING COUNT(*) > 2
ORDER BY department_id;

9.3 ROLLUP, CUBE, GROUPING SETS

SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY ROLLUP (department_id, job_id);
SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY CUBE (department_id, job_id);
SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY GROUPING SETS ((department_id), (job_id), ());

SELECT DECODE(GROUPING(department_id), 1, 'ALL DEPTS', department_id) dept,
       GROUPING_ID(department_id, job_id) gid, GROUP_ID(), SUM(salary)
FROM employees GROUP BY ROLLUP (department_id, job_id);

9.4 String Aggregation

SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS names,
       LISTAGG(DISTINCT job_id, ',') WITHIN GROUP (ORDER BY job_id) AS jobs,       -- 19c
       LISTAGG(last_name, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP (ORDER BY last_name)
FROM employees GROUP BY department_id;

9.5 Other Aggregates

KEEP (DENSE_RANK FIRST/LAST ORDER BY ...):

COLLECT, JSON_ARRAYAGG, JSON_OBJECTAGG, XMLAGG, CORR, COVAR_POP, REGR_*, STATS_* statistical functions.

23ai: BOOLEAN aggregates BOOL_AND / BOOL_OR, CHECKSUM, KURTOSIS_*, SKEWNESS_*.

SELECT department_id,
       MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY salary DESC) AS top_earner
FROM employees GROUP BY department_id;