📊 Aggregation
GROUP BY patterns, windowed ranking and string aggregation with LISTAGG.
| Snippet | |
|---|---|
| ☆Count Employees — Count all rows using COUNT(*) |
SELECT COUNT(*) FROM employees;
|
| ☆Salary Stats per Dept — Show salary summary statistics grouped by department |
SELECT department_id, COUNT(*), AVG(salary), MAX(salary), MIN(salary) FROM employees GROUP BY department_id;
|
| ☆LISTAGG (Names by Dept) — Concatenate group values into one string |
SELECT department_id, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS names FROM employees GROUP BY department_id;
|
| ☆RANK() by Salary — Ties share a rank; next rank is skipped |
SELECT empno, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
|
| ☆DENSE_RANK() by Dept — Rank within each department without gaps |
SELECT empno, deptno, salary, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY salary DESC) AS dept_dense_rank FROM employees;
|
| ☆Total Salary (SUM) — Total of a numeric column |
SELECT SUM(salary) FROM employees;
|
| ☆Average Salary (AVG) — Average of a numeric column |
SELECT AVG(salary) FROM employees;
|
| ☆Max Salary per Dept — Highest salary per department |
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;
|