الدوال التجميعية (Aggregate Functions)

COUNT(*)              -- عد جميع الصفوف (بما فيها NULL)
COUNT(column_name)    -- عد الصفوف التي ليست NULL في هذا العمود
COUNT(DISTINCT column_name)  -- عد القيم الفريدة (بدون تكرار)

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

SELECT COUNT(*) AS total_employees
FROM employees;

+-----------------+
| total_employees |
+-----------------+
| 6               |
+-----------------+

SELECT 
    COUNT(*) AS total_employees,
    COUNT(department) AS employees_with_department
FROM employees;

+-----------------+--------------------------+
| total_employees | employees_with_department|
+-----------------+--------------------------+
| 6               | 5                        |
+-----------------+--------------------------+

-- سارة ليس لديها قسم (NULL)، لذلك لم تُحسب

SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;

+---------------------+
| unique_departments  |
+---------------------+
| 2                   |
+---------------------+

-- الأقسام: IT, HR (بدون تكرار)

SELECT SUM(salary) AS total_salaries
FROM employees;

+----------------+
| total_salaries |
+----------------+
| 35800          |
+----------------+

-- 5000 + 6000 + 5500 + 7000 + 6500 + 5800 = 35800

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

SELECT 
    SUM(quantity) AS total_items_sold,
    SUM(quantity * price) AS total_revenue
FROM sales;

+------------------+---------------+
| total_items_sold | total_revenue |
+------------------+---------------+
| 20               | 10500         |
+------------------+---------------+

-- الكمية: 2+10+3+5 = 20
-- الإيرادات: (2×3500)+(10×50)+(3×800)+(5×120) = 10500

SELECT AVG(salary) AS average_salary
FROM employees;

+----------------+
| average_salary |
+----------------+
| 5966.67        |
+----------------+

-- 35800 / 6 = 5966.67

+----+----------+-------+
| id | student  | score |
+----+----------+-------+
| 1  | أحمد     | 85    |
| 2  | فاطمة    | 92    |
| 3  | محمد     | 78    |
| 4  | سارة     | 95    |
| 5  | علي      | NULL  | ← غائب
+----+----------+-------+

SELECT 
    COUNT(*) AS total_students,
    COUNT(score) AS students_attended,
    AVG(score) AS average_score
FROM grades;

+----------------+-------------------+---------------+
| total_students | students_attended | average_score |
+----------------+-------------------+---------------+
| 5              | 4                 | 87.50         |
+----------------+-------------------+---------------+

-- المتوسط: (85+92+78+95) / 4 = 87.50
-- لاحظ: تم تجاهل NULL (علي غائب)

SELECT MIN(salary) AS lowest_salary
FROM employees;

+---------------+
| lowest_salary |
+---------------+
| 5000          |
+---------------+

+----+------------+------------+
| id | customer   | order_date |
+----+------------+------------+
| 1  | أحمد       | 2024-01-15 |
| 2  | فاطمة      | 2024-01-10 |
| 3  | محمد       | 2024-01-20 |
+----+------------+------------+

SELECT 
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order
FROM orders;

+-------------+------------+
| first_order | last_order |
+-------------+------------+
| 2024-01-10  | 2024-01-20 |
+-------------+------------+

SELECT MAX(salary) AS highest_salary
FROM employees;

+----------------+
| highest_salary |
+----------------+
| 7000           |
+----------------+

SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salaries,
    AVG(salary) AS average_salary,
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;

+-----------------+----------------+----------------+---------------+----------------+
| total_employees | total_salaries | average_salary | lowest_salary | highest_salary |
+-----------------+----------------+----------------+---------------+----------------+
| 6               | 35800          | 5966.67        | 5000          | 7000           |
+-----------------+----------------+----------------+---------------+----------------+

SELECT 
    COUNT(*) AS it_employees,
    AVG(salary) AS it_avg_salary
FROM employees
WHERE department = 'IT';

+--------------+----------------+
| it_employees | it_avg_salary  |
+--------------+----------------+
| 3            | 5666.67        |
+--------------+----------------+

-- موظفو IT: أحمد (5000)، محمد (5500)، علي (6500)
-- المتوسط: (5000+5500+6500) / 3 = 5666.67

SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_order
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

SELECT 
    COUNT(*) AS total_products,
    SUM(stock) AS total_items_in_stock,
    AVG(price) AS average_price,
    MIN(price) AS cheapest_product,
    MAX(price) AS most_expensive_product,
    SUM(stock * price) AS total_inventory_value
FROM products;

SELECT 
    COUNT(*) AS total_students,
    COUNT(score) AS students_attended,
    COUNT(*) - COUNT(score) AS students_absent,
    AVG(score) AS class_average,
    MIN(score) AS lowest_score,
    MAX(score) AS highest_score,
    MAX(score) - MIN(score) AS score_range
FROM exam_results;

SELECT 
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_transaction,
    MIN(amount) AS smallest_sale,
    MAX(amount) AS largest_sale
FROM daily_sales
WHERE sale_date = CURDATE();

SELECT 
    COUNT(*) AS total_employees,
    COUNT(DISTINCT department) AS total_departments,
    SUM(salary) AS monthly_payroll,
    AVG(salary) AS average_salary,
    MIN(hire_date) AS first_hire,
    MAX(hire_date) AS latest_hire,
    AVG(YEAR(CURDATE()) - YEAR(hire_date)) AS avg_years_of_service
FROM employees
WHERE status = 'active';

SELECT 
    AVG(salary) AS avg_raw,
    ROUND(AVG(salary)) AS avg_rounded,
    ROUND(AVG(salary), 2) AS avg_two_decimals
FROM employees;

+-------------+--------------+------------------+
| avg_raw     | avg_rounded  | avg_two_decimals |
+-------------+--------------+------------------+
| 5966.666667 | 5967         | 5966.67          |
+-------------+--------------+------------------+

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

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

-- ✅ صحيح: استخدم فقط الدوال التجميعية
SELECT AVG(salary) AS average_salary
FROM employees;

-- أو استخدم GROUP BY (سنتعلمها في الدرس القادم)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- ❌ خطأ: SUM تعمل فقط على الأرقام
SELECT SUM(name) FROM employees;

-- ✅ صحيح: استخدم COUNT للعد
SELECT COUNT(name) FROM employees;

-- سيء: غير واضح
SELECT COUNT(*), AVG(salary) FROM employees;

-- جيد: واضح ومفهوم
SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary
FROM employees;