SQL Server Interview Question #100

What is the main difference between GROUP BY and window functions?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Quick Interview Answer

GROUP BY reduces rows into one result row per group, while window functions usually preserve the original row-level detail and add calculations across related rows. Use GROUP BY when the required output is a summary such as one row per category. Use a window function when each product or transaction must remain visible while also showing information such as category average, ranking, running total, or previous value. Both approaches can be combined in advanced reporting queries, but they solve different logical problems.

Detailed Explanation

GROUP BY reduces rows into one result row per group, while window functions usually preserve the original row-level detail and add calculations across related rows.

Use GROUP BY when the required output is a summary such as one row per category. Use a window function when each product or transaction must remain visible while also showing information such as category average, ranking, running total, or previous value.

Both approaches can be combined in advanced reporting queries, but they solve different logical problems.

Code Example

-- GROUP BY: one row per category
SELECT CategoryId,
       AVG(Price) AS AveragePrice
FROM dbo.Products
GROUP BY CategoryId;

-- Window function: every product remains visible
SELECT Id,
       Name,
       CategoryId,
       Price,
       AVG(Price) OVER
       (
           PARTITION BY CategoryId
       ) AS CategoryAverage
FROM dbo.Products;