-- COUNT(*): عدّ جميع الصفوف (بما في ذلك الصفوف التي تحتوي على NULL)
SELECT COUNT(*) AS total_employees FROM employees;

-- COUNT(column_name): عدّ الصفوف التي لا تحتوي العمود على NULL
SELECT COUNT(email) AS has_email FROM employees;

SELECT SUM(salary) AS total_salary FROM employees;

SELECT AVG(salary) AS avg_salary FROM employees;

SELECT MAX(salary) AS highest, MIN(salary) AS lowest FROM employees;

-- لنفترض أن جدول employees يحتوي على 10 صفوف، مع 3 قيم NULL في عمود email
SELECT COUNT(*) AS total,        -- 10
       COUNT(email) AS has_email -- 7
FROM employees;

-- كم عدد الأقسام المميزة التي لديها موظفون؟
SELECT COUNT(DISTINCT department_id) AS dept_count FROM employees;

-- مجموع جميع الرواتب المميزة (مجموع بدون تكرار)
SELECT SUM(DISTINCT salary) AS unique_salary_sum FROM employees;

-- الصيغة الأساسية للدالة التجميعية
SELECT aggregate_function(column_name) AS alias
FROM table_name
WHERE condition;

-- يمكن استخدام عدة دوال تجميعية في نفس الوقت
SELECT COUNT(*) AS total_count,
       SUM(column_name) AS total_sum,
       AVG(column_name) AS average,
       MAX(column_name) AS maximum,
       MIN(column_name) AS minimum
FROM table_name;

-- إزالة التكرار باستخدام DISTINCT في التجميع
SELECT COUNT(DISTINCT column_name) AS distinct_count
FROM table_name;

SELECT COUNT(*) AS total_employees,
       COUNT(email) AS has_email,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees;

total_employees  has_email  total_salary  avg_salary  highest_salary  lowest_salary
---------------  ---------  -----------  ----------  --------------  -------------
8                7          112000.00    14000.00    20000.00        9000.00

-- كم عدد موظفي قسم التكنولوجيا؟ وكم عدد الرواتب المميزة؟
SELECT COUNT(*) AS tech_dept_count,
       COUNT(DISTINCT salary) AS distinct_salary_count,
       AVG(salary) AS tech_dept_avg_salary
FROM employees
WHERE department_id = 1;

tech_dept_count  distinct_salary_count  tech_dept_avg_salary
---------------  ---------------------  --------------------
3                3                      17666.67

-- إحصائيات مبالغ الطلبات: الطلبات المكتملة فقط
SELECT COUNT(*) AS order_count,
       SUM(total_amount) AS total_amount,
       AVG(total_amount) AS avg_order_amount,
       MAX(total_amount) AS largest_order,
       MIN(total_amount) AS smallest_order
FROM orders
WHERE status = 'completed';

order_count  total_amount  avg_order_amount  largest_order  smallest_order
-----------  -----------  ----------------  -------------  --------------
5            42500.00     8500.00           15000.00       2800.00

SELECT 
    COUNT(*) AS total_headcount,
    COUNT(email) + COUNT(phone) AS total_contacts,
    SUM(salary) AS annual_salary_expense,
    AVG(salary) AS avg_monthly_salary,
    MAX(salary) - MIN(salary) AS max_salary_gap
FROM employees
WHERE hire_date >= '2024-01-01';

SELECT 
    COUNT(*) AS shipped_order_count,
    COUNT(DISTINCT customer_id) AS unique_customer_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value,
    MAX(total_amount) AS largest_order,
    MIN(total_amount) AS smallest_order
FROM orders
WHERE status = 'shipped';