Difference between windows and aggregate functions in sql.

Basically these two are functions used in sql database. They differ with each other in terms of usage.

1. Aggregate functions. Aggregate functions operate on a set of values to return a single scalar value. These are SQL aggregate functions:

• AVG() returns the average of the specified values.
• SUM() calculates the sum of all values in the set.
• MAX() and MIN() return the maximum and minimum value, respectively.
• COUNT() returns the total number of values in the set.

for example using this employee table

To use function count()

use this syntax;

SELECT COUNT(*) AS total_employees
FROM emp_table et 
WHERE department = 'IT';

The syntax will return this :

1. windows function: window function in SQL performs calculations across a set of rows related to the current row without collapsing the results like group by.

using the Employee table lets use the windows function to calculate

using windows function rank employees by salary

SELECT
    employee_id,
    first_name,
    last_name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM emp_table;

The result from the above syntax: