GROUP BY vs PARTITION BY in SQL

-

GROUP BY vs PARTITION BY in SQL — What's the Difference?

Both GROUP BY and PARTITION BY organize rows into groups — but they do fundamentally different things with those groups. Mixing them up is one of the most common points of confusion once you move past basic SQL.

📘 The Core Difference

GROUP BY collapses rows: multiple input rows become one output row per group. You lose the individual row-level detail.

PARTITION BY (used with window functions) keeps every row, but lets you calculate something "within" each group without collapsing anything.

🧪 GROUP BY in Action

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Output: one row per department. If you had 50 employees across 5 departments, you get back exactly 5 rows.

🧪 PARTITION BY in Action

SELECT
  employee_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

Output: all 50 employee rows, unchanged — but each row now also shows the average salary for its department, calculated "over" that partition. Nothing collapses.

🎯 Side-by-Side Comparison

employee_namedepartmentsalarydept_avg_salary (PARTITION BY)
AliceEngineering90008500
BobEngineering80008500
CarolSales70007000

With GROUP BY, you'd only get back "Engineering: 8500" and "Sales: 7000" — you'd lose Alice, Bob, and Carol's individual rows entirely.

🛠️ When to Use Which

  • Use GROUP BY when you want a summary — total sales per region, average order value per customer, count of orders per day.
  • Use PARTITION BY when you need row-level detail alongside a group-level calculation — "show me each employee's salary next to their department average," "rank each product within its category," "show each order alongside the customer's running total."

🔍 A Practical Combined Example

Finding each employee who earns above their department's average — this needs PARTITION BY specifically, because GROUP BY alone can't compare an individual row to a group aggregate in the same query without a subquery:

SELECT * FROM (
  SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg;

⚠️ Common Mistakes

  • Reaching for a self-join or subquery to compare a row to its group average, when PARTITION BY does it directly and more efficiently
  • Trying to use GROUP BY when you actually need individual row detail preserved — realizing too late that your report lost row-level granularity
  • Forgetting that PARTITION BY requires an OVER() clause — it only works alongside a window function, not as a standalone clause

🔗 Related Posts

  • SQL Window Functions Explained: ROW_NUMBER, RANK, LAG, LEAD
  • SQL WHERE vs HAVING: When to Use Each

Comments

Post a Comment

Popular Posts

BigQuery MERGE Statement – Explained

-
Image
BigQuery MERGE Statement – Explained with Syntax & Use Cases BigQuery MERGE Statement – Explained 📘 What is BigQuery MERGE? The MERGE statement in BigQuery allows you to conditionally INSERT , UPDATE , or DELETE rows in a target table based on matching rows from a source table. It’s ideal for data synchronization, staging table merges, and incremental updates. 🖼️ Thumbnail Preview 🧪 Syntax Overview MERGE target_table AS T USING source_table AS S ON T.id = S.id WHEN MATCHED THEN UPDATE SET T.name = S.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (S.id, S.name); 🎯 Example Use Case MERGE dataset.inventory AS I USING dataset.new_arrivals AS N ON I.product_id = N.product_id WHEN MATCHED THEN UPDATE SET I.quantity = I.quantity + N...
Read more

LRT Masjid Jamek-Bandaraya

-
Image
LRT Ampang Line Closure – Masjid Jamek to Bandaraya Suspension Update LRT Ampang Line Update – Service Suspension Extended The section of the LRT Ampang Line between Masjid Jamek and Bandaraya stations will remain closed for an additional two weeks following the discovery of significant structural damage on an overhead viaduct near Bandaraya. Transport Minister Anthony Loke stated that the line will reopen only after corrective measures and safety assessments are fully completed. “Once it is okay and safety is ensured, we will reopen the line. I apologise to all commuters who are affected,” he added. Public transport operator Rapid Rail has engaged a professional engineering firm to conduct a detailed investigation of the cracked viaduct and pier. This probe will help determine the source of damage and inform the restoration strategy. 🚍 S...
Read more

Kavalkaran - MGR Tamil movie Part 4

-
Kavalkaran (1967) - MGR Tamil movie Please subscribe to channel & comment ur favourite puratchi thalaivar movie Kaavalkaaran (1967) - M.G.R | Tamil Full movie Kaavalkaaran is a 1967 Indian Tamil-language spy film, directed by P. Neelakantan and produced and co-written by R. M. Veerappan. The film stars M. G. Ramachandran and Jayalalithaa, with M. N. Nambiar, S. A. Ashokan, Sivakumar and Nagesh in supporting roles. It was released on 7 September 1967. The film became a major success, and Ramachandran considered it his ninth turning point. As Per wikipedia , Before Kaavalkaaran began production, MGR's voice had been damaged due to a gunshot to his throat caused by M. R. Radha. Despite this, MGR refused others' suggestion to use a dubbing artiste, and dubbed in his own voice. Check out more videos like ...
Read more