Important SQL Topics : PART 2- (Window Aggregate Function with Example ) SUM, AVG, COUNT, MIN, MAX

SQL Window Aggregate Functions: An Overview

Window aggregate functions are a powerful feature in SQL that allow you to perform calculations across a set of table rows that are somehow related to the current row. These functions do not group the results into a single output row like regular aggregate functions (e.g., SUM, AVG), but rather return the result of the aggregate calculation for each row.

Key Concepts

1. Window Functions: Functions that operate over a subset of rows related to the current row.
2. Window Specification: Defines the subset of rows the function should operate on.
3. Partitioning: Divides the result set into partitions to which the window function is applied.
4. Ordering: Defines the order of rows within each partition.
5. Frame Definition: Defines a sliding window frame within the partition.

Common Window Aggregate Functions

SUM(), AVG(), MIN(), MAX(), COUNT()

Syntax

<window_function>() OVER (
    [PARTITION BY <expr1>, <expr2>, ...]
    [ORDER BY <expr1> [ASC|DESC], <expr2> [ASC|DESC], ...]
    [ROWS|RANGE BETWEEN <frame_start> AND <frame_end>]
)        

Example Usage

Let's use a sample sales table to illustrate how window aggregate functions work.

Sample Data

CREATE TABLE sales (
    id INT,
    sales_person VARCHAR(50),
    region VARCHAR(50),
    sale_amount DECIMAL(10, 2),
    sale_date DATE
);

INSERT INTO sales (id, sales_person, region, sale_amount, sale_date) VALUES
(1, 'Alice', 'East', 100.00, '2023-01-01'),
(2, 'Bob', 'West', 150.00, '2023-01-01'),
(3, 'Alice', 'East', 200.00, '2023-01-02'),
(4, 'Bob', 'West', 300.00, '2023-01-02'),
(5, 'Alice', 'East', 50.00, '2023-01-03'),
(6, 'Bob', 'West', 400.00, '2023-01-03');        

Example Queries

• Calculating Running SUM Sales by Each Salesperson

SELECT
    sales_person,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (
        PARTITION BY sales_person
        ORDER BY sale_date
    ) AS running_total
FROM sales;        

SELECT sales_person, sale_date, sale_amount, SUM(sale_amount) OVER ( PARTITION BY sales_person ORDER BY sale_date ) AS running_total FROM sales;

Explanation:

• PARTITION BY sales_person: Calculates the running total for each salesperson separately.
• ORDER BY sale_date: Orders the rows by sale date within each partition.

Result:

• Calculating Average Sale Amount by Region

SELECT
    sales_person,
    region,
    sale_amount,
    AVG(sale_amount) OVER (
        PARTITION BY region
    ) AS avg_sale_amount_by_region
FROM sales;        

Explanation:

• PARTITION BY region: Calculates the average sale amount for each region separately.

Result:

• Calculating Maximum Sale Amount Over All Rows

SELECT
    sales_person,
    sale_date,
    sale_amount,
    MAX(sale_amount) OVER () AS max_sale_amount
FROM sales;        

Explanation:

• The window function has no PARTITION BY or ORDER BY clause, so it calculates the maximum sale amount over all rows.

Result:

• The window function has no PARTITION BY or ORDER BY clause, so it calculates the maximum sale amount over all rows.
• Calculating Minimum Sale Amount Over All RowsMIN():

SELECT
    sales_person,
    sale_date,
    sale_amount,
    MIN(sale_amount) OVER () AS max_sale_amount
FROM sales;        

Explanation:

• The window function has no PARTITION BY or ORDER BY clause, so it calculates the minimum sale amount over all rows.

Result:

• Calculating Maximum Sale Amount Over All Rows

COUNT(): Count the number of sales up to the current row for each salesman.

SELECT 
    salesman, 
    sale_date, 
    sale_amount,
    COUNT(*) OVER (
        PARTITION BY salesman 
        ORDER BY sale_date
    ) AS running_count
FROM 
    sales;        

Explanation:

• The window function has no PARTITION BY or ORDER BY clause, so it calculates the count of salesman over all rows.

Result:

Summary:

Window aggregate functions in SQL provide a powerful way to perform complex calculations over a set of rows that are related to the current row. By using the OVER clause with partitioning and ordering options, you can compute running totals, moving averages, cumulative sums, and more, making it easier to analyze and gain insights from your data.

A heartfelt thank you to Rishabh Mishra for expertly guiding us through the concept of SQL. Your insights have been invaluable. Window functions on your YouTube channel?? www.youtube.com/@RishabhMishraOfficial

Stay tuned for Part 3, coming soon.

For more in-depth discussions on crucial SQL topics, be sure to follow

Roshni Gulechha. I am continuously working on improving the quality of my posts and presentations to provide even better content.

#SQL #DataAnalysis #Learning