🍿 CornPop

GROUP BY

3 min read

The GROUP BY clause is used to group rows with the same values in specified columns into summary rows, often used with Aggregate functions (e.g., COUNT, SUM, AVG, MAX, MIN) to perform calculations on groups of data. منقدرش نستخدمها مع الـ WHERE انما نقدر نستخدمها مع HAVING بس

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Key Points:

  1. Purpose: Groups rows based on column values.
  2. Aggregate Functions: Works with aggregate functions to provide meaningful summaries for each group.
  3. Mandatory for Aggregates on Grouped Data: If a column is not in an aggregate function, it must be in the GROUP BY clause.

Examples:

1. Basic Grouping

Group employees by their department and calculate the total salary for each department:

SELECT department, SUM(salary) AS total_salary
FROM Employees
GROUP BY department;

Output:

departmenttotal_salary
Sales150000
HR80000
IT120000

2. Grouping with Multiple Columns

Group employees by department and job title, and calculate the average salary:

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

Output:

departmentjob_titleavg_salary
SalesSales Manager75000
HRHR Specialist40000
ITSoftware Engineer60000

3. Filtering Groups Using HAVING

The HAVING clause filters groups after grouping, similar to how WHERE filters rows before grouping.

Find departments with total salaries greater than 100,000:

SELECT department, SUM(salary) AS total_salary
FROM Employees
GROUP BY department
HAVING SUM(salary) > 100000;

Output:

departmenttotal_salary
Sales150000
IT120000

4. Using GROUP BY with COUNT

Count the number of employees in each department:

SELECT department, COUNT(employee_id) AS num_employees
FROM Employees
GROUP BY department;

Output:

departmentnum_employees
Sales3
HR2
IT2

Group by + Order by

لو عايز تستخدم GROUP BY مع ORDER BY في استعلام SQL Server، بتجمع القيم على حسب عمود معين باستخدام GROUP BY، وبعد كده ترتب النتائج باستخدام ORDER BY.

الصيغة العامة:

SELECT column1, aggregate_function(column2) AS alias
FROM table_name
GROUP BY column1
ORDER BY column1 [ASC|DESC];

شرح:

  1. الـcolumn1: العمود اللي هتعمل عليه التجميع.
  2. الـaggregate_function(column2): دالة تجميعية زي SUM, COUNT, AVG, MAX, أو MIN اللي بتشتغل على عمود معين.
  3. الـGROUP BY column1: بتحدد العمود اللي هتعمل عليه التجميع.
  4. الـORDER BY column1 [ASC|DESC]: ترتيب النتائج تصاعديًا (ASC) أو تنازليًا (DESC).

مثال عملي:

الهدف:

لو عندك جدول Sales وفيه الأعمدة ProductID وQuantity، وعايز:

  • تجمع الكميات (Quantity) لكل منتج.
  • ترتب المنتجات حسب الكمية تنازليًا.
الكود:
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID
ORDER BY TotalQuantity DESC;
النتيجة:
  • كل صف هيمثل منتج واحد (ProductID) مع إجمالي الكمية المباعة (TotalQuantity).
  • المنتجات مرتبة تنازليًا حسب إجمالي الكمية.

Common Use Cases:

  1. Summarizing Data: Calculating totals, averages, or other summaries for specific groups (e.g., revenue by region).
  2. Counting Records: Determining the count of items within each group (e.g., number of orders per customer).
  3. Filtering Summarized Data: Using HAVING to filter groups based on aggregate values (e.g., only show departments with more than 5 employees).

Difference Between WHERE and HAVING:

AspectWHEREHAVING
PurposeFilters rows before grouping.Filters groups after grouping.
Works OnIndividual rows.Aggregated data (e.g., SUM, COUNT).
ExampleWHERE salary > 50000HAVING SUM(salary) > 100000

Summary:

  • GROUP BY: Organizes data into groups for analysis.
  • Aggregate Functions: Used to calculate metrics like SUM, COUNT, or AVG within groups.
  • HAVING Clause: Filters groups based on aggregated values.
  • A vital tool for summarizing and analyzing structured data in SQL.

Graph View

Backlinks