SQL: استعلامات التجميع

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

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

department_id  headcount
-------------  ---------
1              3
2              2
3              2
NULL           1

-- متوسط الراتب لكل قسم
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

SELECT department_id,
       COUNT(*) AS headcount,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees
GROUP BY department_id;

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

department_id  status    headcount
-------------  --------  ---------
1              active    3
2              active    1
2              inactive  1
3              active    2

-- متوسط الراتب حسب القسم، مرتب من الأعلى إلى الأدنى
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

-- WHERE: تصفية الصفوف أولاً، ثم التجميع
-- إيجاد الأقسام التي لديها أكثر من 3 موظفين
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE salary > 5000       -- أولاً استبعاد الصفوف التي رواتبها أقل من 5000
GROUP BY department_id;

-- HAVING: التجميع أولاً، ثم تصفية المجموعات
-- إيجاد الأقسام التي لديها 3 موظفين أو أكثر
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3;     -- تصفية المجموعات التي عدد أعضائها أقل من 3

-- الجمع بين WHERE + HAVING
-- إيجاد الأقسام التي لديها موظفون تم تعيينهم في 2024 ومتوسط راتب > 10000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'   -- تصفية الصفوف أولاً
GROUP BY department_id
HAVING AVG(salary) > 10000;      -- ثم تصفية المجموعات

ترتيب الكتابة:  SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
ترتيب التنفيذ: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

-- التجميع الأساسي
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

-- التجميع بأعمدة متعددة
SELECT column1, column2, aggregate_function(column_name)
FROM table_name
GROUP BY column1, column2;

-- WHERE + GROUP BY + HAVING
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE row_level_condition
GROUP BY column_name
HAVING aggregate_level_condition
ORDER BY sort_column;

SELECT d.department_name,
       COUNT(e.employee_id) AS headcount,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS total_salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY total_salary DESC;

department_name  headcount  avg_salary  total_salary
---------------  ---------  ----------  ------------
Technology       3          17666.67    53000.00
Finance          2          13500.00    27000.00
Marketing        2          11500.00    23000.00
Administration   1          9000.00     9000.00

-- من بين الموظفين الذين تم تعيينهم في 2024، إيجاد الأقسام بمتوسط راتب > 12000
SELECT department_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'
GROUP BY department_id
HAVING AVG(salary) > 12000
ORDER BY avg_salary DESC;

department_id  headcount  avg_salary
-------------  ---------  ----------
1              2          18500.00
3              1          14000.00

-- إحصائيات الموظفين حسب القسم والحالة، عرض المجموعات التي عدد أعضائها 2 أو أكثر فقط
SELECT d.department_name,
       e.status,
       COUNT(*) AS headcount,
       MIN(e.salary) AS lowest_salary,
       MAX(e.salary) AS highest_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 0
GROUP BY d.department_name, e.status
HAVING COUNT(*) >= 2
ORDER BY d.department_name, headcount DESC;

department_name  status  headcount  lowest_salary  highest_salary
---------------  ------  ---------  -------------  --------------
Finance          active  2          12000.00       14000.00
Marketing        active  2          10000.00       12000.00
Technology       active  3          15000.00       20000.00

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_amount,
    AVG(total_amount) AS avg_order_amount
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
HAVING COUNT(*) >= 3
ORDER BY year, month;

SELECT d.department_name,
       COUNT(e.employee_id) AS headcount,
       AVG(e.salary) AS dept_avg_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees)
ORDER BY dept_avg_salary DESC;