4. GROUP BY VS PARTITION BY

GROUP BY

• Purpose: Aggregates data into summary rows based on one or more columns.
• Result: Returns one row per group.
• Usage: Commonly used with aggregate functions like SUM(), COUNT(), AVG(), etc.

Category	Sales	SaleDate
Electronics	100	2025-01-01
Electronics	200	2025-01-02
Electronics	150	2025-01-03
Furniture	300	2025-01-01
Furniture	100	2025-01-02
Furniture	400	2025-01-03

SELECT 
    Category,
    SUM(Sales) AS TotalSales
FROM 
    SalesTable
GROUP BY 
    Category;

\

Result:

Category	TotalSales
Electronics	450
Furniture	800

Visual Representation for GROUP BY:

• Electronics: 450
• Furniture: 800

---

PARTITION BY

• Purpose: Divides the result set into partitions (subsets) for a specific column, but it does not collapse rows like GROUP BY.
• Result: All rows are retained, and aggregate functions are applied over each partition.
• Usage: Commonly used with window functions like ROW_NUMBER(), RANK(), SUM() OVER().

SELECT 
    Category,
    Sales,
    SUM(Sales) OVER (PARTITION BY Category ORDER BY SaleDate) AS RunningTotal
FROM 
    SalesTable;

Result:

Category	Sales	RunningTotal
Electronics	100	100
Electronics	200	300
Electronics	150	450
Furniture	300	300
Furniture	100	400
Furniture	400	800

A line chart showing the running totals for each category over time:

• Electronics: 100 → 300 → 450
• Furniture: 300 → 400 → 800

---

Key Differences:

Feature	GROUP BY	PARTITION BY
Collapses rows	Yes, groups rows into a single result row.	No, retains all rows in the result set.
Aggregates scope	Across groups.	Across partitions, per row.
Result	One row per group.	One row per original row, with partition info.