التجميع GROUP BY

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

+----+----------+----------+--------+
| id | product  | quantity | price  |
+----+----------+----------+--------+
| 1  | لابتوب   | 2        | 3500   |
| 2  | ماوس     | 10       | 50     |
| 3  | لابتوب   | 1        | 3500   |
| 4  | شاشة     | 3        | 800    |
| 5  | ماوس     | 5        | 50     |
| 6  | لابتوب   | 3        | 3500   |
+----+----------+----------+--------+

SELECT SUM(quantity) AS total_quantity
FROM sales;

+----------------+
| total_quantity |
+----------------+
| 24             |
+----------------+

-- مجموع كل المبيعات: 2+10+1+3+5+3 = 24

SELECT 
    product,
    SUM(quantity) AS total_quantity
FROM sales
GROUP BY product;

+----------+----------------+
| product  | total_quantity |
+----------+----------------+
| لابتوب   | 6              | ← 2+1+3
| ماوس     | 15             | ← 10+5
| شاشة     | 3              |
+----------+----------------+

+----+----------+--------+------------+
| id | name     | salary | department |
+----+----------+--------+------------+
| 1  | أحمد     | 5000   | IT         |
| 2  | فاطمة    | 6000   | HR         |
| 3  | محمد     | 5500   | IT         |
| 4  | سارة     | 7000   | Sales      |
| 5  | علي      | 6500   | IT         |
| 6  | نورة     | 5800   | HR         |
| 7  | خالد     | 6200   | Sales      |
+----+----------+--------+------------+

SELECT 
    department AS القسم,
    COUNT(*) AS عدد_الموظفين
FROM employees
GROUP BY department
ORDER BY عدد_الموظفين DESC;

+------------+----------------+
| القسم      | عدد_الموظفين  |
+------------+----------------+
| IT         | 3              |
| HR         | 2              |
| Sales      | 2              |
+------------+----------------+

SELECT 
    customer_name,
    COUNT(*) AS total_orders
FROM orders
GROUP BY customer_name
ORDER BY total_orders DESC;

SELECT 
    department,
    SUM(salary) AS total_salaries,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

+------------+----------------+----------------+
| department | total_salaries | employee_count |
+------------+----------------+----------------+
| IT         | 17000          | 3              | ← 5000+5500+6500
| HR         | 11800          | 2              | ← 6000+5800
| Sales      | 13200          | 2              | ← 7000+6200
+------------+----------------+----------------+

SELECT 
    product,
    SUM(quantity) AS total_sold,
    SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product
ORDER BY total_revenue DESC;

+----------+------------+---------------+
| product  | total_sold | total_revenue |
+----------+------------+---------------+
| لابتوب   | 6          | 21000         | ← 6 × 3500
| شاشة     | 3          | 2400          | ← 3 × 800
| ماوس     | 15         | 750           | ← 15 × 50
+----------+------------+---------------+

SELECT 
    department,
    COUNT(*) AS employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

+------------+-----------+------------+------------+------------+
| department | employees | avg_salary | min_salary | max_salary |
+------------+-----------+------------+------------+------------+
| Sales      | 2         | 6600.00    | 6200       | 7000       |
| HR         | 2         | 5900.00    | 5800       | 6000       |
| IT         | 3         | 5666.67    | 5000       | 6500       |
+------------+-----------+------------+------------+------------+

+----+----------+----------+----------+--------+
| id | product  | city     | quantity | price  |
+----+----------+----------+----------+--------+
| 1  | لابتوب   | الرياض   | 2        | 3500   |
| 2  | لابتوب   | جدة      | 1        | 3500   |
| 3  | ماوس     | الرياض   | 10       | 50     |
| 4  | ماوس     | جدة      | 5        | 50     |
| 5  | لابتوب   | الرياض   | 3        | 3500   |
+----+----------+----------+----------+--------+

SELECT 
    product,
    city,
    SUM(quantity) AS total_quantity,
    SUM(quantity * price) AS total_revenue
FROM sales_detailed
GROUP BY product, city
ORDER BY product, city;

+----------+----------+----------------+---------------+
| product  | city     | total_quantity | total_revenue |
+----------+----------+----------------+---------------+
| لابتوب   | الرياض   | 5              | 17500         |
| لابتوب   | جدة      | 1              | 3500          |
| ماوس     | الرياض   | 10             | 500           |
| ماوس     | جدة      | 5              | 250           |
+----------+----------+----------------+---------------+

1. FROM    - اختيار الجدول
2. WHERE   - تصفية الصفوف
3. GROUP BY - تجميع البيانات
4. HAVING  - تصفية المجموعات (سنتعلمها في الدرس القادم)
5. SELECT  - اختيار الأعمدة
6. ORDER BY - ترتيب النتائج
7. LIMIT   - تحديد عدد النتائج

SELECT 
    department,
    COUNT(*) AS high_earners,
    AVG(salary) AS avg_high_salary
FROM employees
WHERE salary > 5500
GROUP BY department;

+------------+--------------+------------------+
| department | high_earners | avg_high_salary  |
+------------+--------------+------------------+
| IT         | 1            | 6500.00          | ← علي فقط
| HR         | 1            | 6000.00          | ← فاطمة فقط
| Sales      | 2            | 6600.00          | ← سارة وخالد
+------------+--------------+------------------+

SELECT 
    product,
    COUNT(*) AS orders_count,
    SUM(quantity) AS total_sold
FROM sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY product;

SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year DESC, month DESC;

SELECT 
    customer_name,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_spent,
    AVG(amount) AS avg_order
FROM orders
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 5;

SELECT 
    class,
    COUNT(*) AS total_students,
    COUNT(score) AS students_attended,
    AVG(score) AS class_average,
    MIN(score) AS lowest_score,
    MAX(score) AS highest_score,
    COUNT(CASE WHEN score >= 50 THEN 1 END) AS passed,
    COUNT(CASE WHEN score < 50 THEN 1 END) AS failed
FROM exam_results
GROUP BY class
ORDER BY class_average DESC;

SELECT 
    category,
    COUNT(*) AS product_count,
    SUM(stock) AS total_stock,
    AVG(price) AS avg_price,
    SUM(stock * price) AS inventory_value
FROM products
GROUP BY category
ORDER BY inventory_value DESC;

SELECT 
    department,
    COUNT(*) AS employees,
    SUM(salary) AS payroll,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(salary) - MIN(salary) AS salary_range,
    AVG(YEAR(CURDATE()) - YEAR(hire_date)) AS avg_tenure
FROM employees
WHERE status = 'active'
GROUP BY department
ORDER BY payroll DESC;

-- ❌ خطأ: name ليس في GROUP BY ولا في دالة تجميعية
SELECT name, department, AVG(salary)
FROM employees
GROUP BY department;

-- ERROR: Column 'name' is not in GROUP BY clause

-- ✅ صحيح: فقط الأعمدة في GROUP BY أو الدوال التجميعية
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- أو إذا أردت الأسماء أيضاً:
SELECT name, department, salary
FROM employees
ORDER BY department;

-- ❌ خطأ: لا يمكن استخدام دالة تجميعية في WHERE
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 6000
GROUP BY department;

-- ERROR: Invalid use of aggregate function

-- ✅ صحيح: استخدم HAVING لتصفية المجموعات
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

-- سنتعلم HAVING بالتفصيل في الدرس القادم

-- سيء
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department;

-- جيد
SELECT 
    department AS القسم,
    COUNT(*) AS عدد_الموظفين,
    AVG(salary) AS متوسط_الراتب
FROM employees
GROUP BY department;

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