Examples of PARTITION BY Clause
Example 1: Rank of Products within Each Category
Query:
SELECT product_id, category, sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rank_within_category
FROM sales;
Output:
Explanation: In the above query, We calculates the rank of each product within its category based on the sales amount, using the RANK()
window function with PARTITION BY
to group products by category.
Example 2: Cumulative Sales Amount within Each Category
SELECT product_id, category, sales_amount,
SUM(sales_amount) OVER (PARTITION BY category ORDER BY product_id) AS cumulative_sales
FROM sales;
Output:
Explanation: In the above query, We calculates the cumulative sales amount for each product within its category, using the SUM
()
window function with PARTITION
BY
to group products by category and ordering by product_id
.
PARTITION BY vs GROUP BY in SQL
In SQL both PARTITION BY and GROUP BY are important clauses used for data aggregation and analysis. Sometimes they work as same but they serve different purposes and are applied in different situations. In this article, we’ll understand both of them along with the syntax, multiple examples for both clauses and also the differences between them.