Examples of PARTITION BY and GROUP BY
To understand PARTITION BY vs GROUP BY in SQL we need a table on which we will perform various operations and queries. Here we will consider a table called sales which contains product_id, category and sales_amount as Columns.
Query:
CREATE TABLE sales (
product_id INT,
category VARCHAR(50),
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales (product_id, category, sales_amount) VALUES (1, 'Electronics' , 500);
INSERT INTO sales (product_id, category, sales_amount) VALUES (2, 'Clothing' , 300);
INSERT INTO sales (product_id, category, sales_amount) VALUES (3, 'Electronics' , 700);
INSERT INTO sales (product_id, category, sales_amount) VALUES (4, 'Clothing' , 400);
INSERT INTO sales (product_id, category, sales_amount) VALUES (5, 'Electronics' , 600);
Our sales table looks like:
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.