Database Essentials - Writing Advanced SQL Queries - Analytic Functions - Aggregations

In this article, we will explore how to perform aggregations within a partition or group using Windowing/Analytics Functions.

Key Concept 1

In cases where we need to calculate aggregated results based on a grouping key, we can use the GROUP BY clause.

SELECT employee_id, department_id, salary
FROM employees
GROUP BY department_id;

Key Concept 2

To obtain raw data alongside aggregated results, using the GROUP BY clause might be complex. Using aggregate functions with OVER clause simplifies the query writing process and improves performance.

SELECT e.employee_id, e.department_id, e.salary,
    ae.department_salary_expense,
    ae.avg_salary_expense
FROM employees e JOIN (
    SELECT department_id, 
        sum(salary) AS department_salary_expense,
        round(avg(salary)::numeric, 2) AS avg_salary_expense
    FROM employees
    GROUP BY department_id
) ae
ON e.department_id = ae.department_id

Hands-On Tasks

List of tasks to apply concepts from the article:

  1. Write a SQL query using the OVER clause for aggregating results within a partition.
  2. Calculate the percentage of employee salary compared to department salary expense.

Conclusion

In conclusion, we have delved into performing aggregations using Analytic Functions and how they simplify and streamline the process in SQL queries. By using OVER clauses, we can efficiently accomplish complex aggregations and calculations within windows or groups.

Click here to watch the video tutorial to supplement the text.

Watch the video tutorial here