How to use Aggregate Function(Sum, Avg) as Window functions
How to use Aggregate Functions as Window functions?
#sqlfunctions #sqltutorialforbeginners #databasemanagement
Linkedin Group: / 14478259
Chapters:
00:00 Introduction
00:11 Overview of Aggregate Functions in SQL
00:24 Using Aggregate Functions as Window Functions in SQL
00:29 Explanation in Hindi
00:34 Aggregate Functions in Window Context in SQL
00:40 Partitioning and Window Functions in SQL
01:00 Practical Example: Minimum Salary Calculation in SQL
02:20 Syntax and Practical Application
03:19 Practical Exercise
04:01 Performing SUM Operation in SQL
04:51 Calculating Averages in SQL
07:58 Counting Records in SQL
08:51 MIN and MAX Functions in SQL
10:03 Validating Results
11:00 Conclusion
We will discuss how to use aggregate functions as window functions in SQL. Aggregate functions like SUM, AVERAGE, COUNT, MIN, and MAX can perform calculations over specified partitions of rows using the OVER clause with the PARTITION BY clause.
For example, if you have a table with employee IDs, salaries, and department IDs, you can calculate the minimum salary for each department by grouping rows by department and using the MIN function.
Here's how you can use these functions:
1.SUM: Calculate the total salaries by region.
SELECT region, SUM(salary) OVER (PARTITION BY region) AS total_sales FROM employees;
2.AVERAGE: Calculate the average salary by region.
SELECT region, AVG(salary) OVER (PARTITION BY region) AS average_salary FROM employees;
3.COUNT: Count the number of employees by region.
SELECT region, COUNT(employee_id) OVER (PARTITION BY region) AS employee_count FROM employees;
4.MIN and MAX: Calculate the minimum and maximum salaries by region.
SELECT region, MIN(salary) OVER (PARTITION BY region) AS min_salary, MAX(salary) OVER (PARTITION BY region) AS max_salary FROM employees;
Practicing these examples will help you understand how to use window functions effectively. Thank you for watching Educate Cube. Please like, share, and subscribe to our channel. Follow us on LinkedIn. See you in the next video. Bye-bye!