تجميع البيانات GROUP BY

COUNT()  -- عدد الصفوف
SUM()    -- مجموع القيم
AVG()    -- المتوسط الحسابي
MAX()    -- أكبر قيمة
MIN()    -- أصغر قيمة

-- إجمالي عدد الموظفين
SELECT COUNT(*) AS total_employees
FROM employees;

-- مجموع الرواتب
SELECT SUM(salary) AS total_salaries
FROM employees;

-- متوسط الرواتب
SELECT AVG(salary) AS average_salary
FROM employees;

-- أعلى وأقل راتب
SELECT MAX(salary) AS highest,
       MIN(salary) AS lowest
FROM employees;

-- كل الإحصائيات معاً
SELECT COUNT(*) AS count,
       SUM(salary) AS total,
       AVG(salary) AS average,
       MAX(salary) AS max_salary,
       MIN(salary) AS min_salary
FROM employees;

-- عدد الموظفين في كل قسم
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

+---------------+----------------+
| department_id | employee_count |
+---------------+----------------+
| 1             | 3              |
| 2             | 3              |
| 3             | 1              |
| 4             | 1              |
+---------------+----------------+

-- عدد الموظفين مع اسم القسم
SELECT d.name AS department,
       COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;

-- إحصائيات الرواتب لكل قسم
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MAX(salary) AS max_salary,
       MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

-- عدد الموظفين حسب القسم والعمر
SELECT department_id,
       age,
       COUNT(*) AS count
FROM employees
GROUP BY department_id, age;

-- المبيعات حسب المنتج والتاريخ
SELECT product_id,
       order_date,
       SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id, order_date;

-- الأقسام التي فيها أكثر من 2 موظفين
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;

-- الأقسام التي متوسط راتب موظفيها (فوق 25 سنة) أكبر من 5000
SELECT department_id,
       AVG(salary) AS avg_salary
FROM employees
WHERE age > 25              -- تصفية قبل التجميع
GROUP BY department_id
HAVING AVG(salary) > 5000;  -- تصفية بعد التجميع

SELECT d.name AS department,
       SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY total_salary DESC
LIMIT 3;

SELECT p.name AS product,
       SUM(o.quantity) AS total_sold,
       SUM(o.quantity * p.price) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id, p.name
ORDER BY total_sold DESC;

SELECT customer_name,
       COUNT(*) AS order_count,
       SUM(quantity) AS total_items
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 1
ORDER BY order_count DESC;

SELECT d.name AS department,
       ROUND(AVG(e.age), 1) AS avg_age
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY avg_age;

-- COUNT(*) - يعد كل الصفوف
SELECT COUNT(*) FROM employees;

-- COUNT(column) - يعد القيم غير NULL
SELECT COUNT(department_id) FROM employees;
-- لن يعد الصفوف التي department_id = NULL

-- COUNT(DISTINCT column) - يعد القيم الفريدة
SELECT COUNT(DISTINCT department_id)
FROM employees;
-- عدد الأقسام الفريدة

SELECT column, AGG(column)   -- 5. اختيار الأعمدة
FROM table                    -- 1. تحديد الجدول
WHERE condition               -- 2. تصفية الصفوف
GROUP BY column               -- 3. التجميع
HAVING condition              -- 4. تصفية المجموعات
ORDER BY column               -- 6. الترتيب
LIMIT n;                      -- 7. تحديد العدد

-- خطأ: عمود غير مُجمّع وغير موجود في GROUP BY
SELECT name, department_id, COUNT(*)
FROM employees
GROUP BY department_id;
-- name ليس في GROUP BY ولا في دالة تجميعية!

-- الصحيح:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- خطأ: استخدام WHERE مع دالة تجميعية
SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 2  -- خطأ!
GROUP BY department_id;

-- الصحيح: استخدم HAVING
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;