شرط HAVING

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition_on_rows
GROUP BY column1
HAVING condition_on_groups
ORDER BY column1;

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

+----+----------+--------+------------+
| 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      |
| 8  | ليلى     | 4500   | IT         |
+----+----------+--------+------------+

-- WHERE: تصفية الموظفين براتب > 5500، ثم التجميع
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE salary > 5500  -- ← تصفية قبل التجميع
GROUP BY department;

+------------+----------------+------------+
| department | employee_count | avg_salary |
+------------+----------------+------------+
| IT         | 1              | 6500.00    | ← علي فقط
| HR         | 2              | 5900.00    | ← فاطمة ونورة
| Sales      | 2              | 6600.00    | ← سارة وخالد
+------------+----------------+------------+

-- HAVING: التجميع أولاً، ثم تصفية الأقسام التي متوسط رواتبها > 5500
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5500;  -- ← تصفية بعد التجميع

+------------+----------------+------------+
| department | employee_count | avg_salary |
+------------+----------------+------------+
| HR         | 2              | 5900.00    |
| Sales      | 2              | 6600.00    |
+------------+----------------+------------+

-- IT لم يظهر لأن متوسط رواتبه (5500) ليس أكبر من 5500

SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;  -- فقط الأقسام بأكثر من موظفين

+------------+----------------+------------+
| department | employee_count | avg_salary |
+------------+----------------+------------+
| IT         | 4              | 5375.00    |
+------------+----------------+------------+

-- فقط IT لديه 4 موظفين (أحمد، محمد، علي، ليلى)

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

SELECT 
    department,
    COUNT(*) AS employees,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 15000
ORDER BY total_payroll DESC;

+------------+-----------+---------------+
| department | employees | total_payroll |
+------------+-----------+---------------+
| IT         | 4         | 21500         | ← 5000+5500+6500+4500
+------------+-----------+---------------+

SELECT 
    product_name,
    COUNT(*) AS orders_count,
    SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(quantity * price) > 10000
ORDER BY total_revenue DESC;

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
HAVING AVG(salary) > 6000;

+------------+-----------+------------+------------+------------+
| department | employees | avg_salary | min_salary | max_salary |
+------------+-----------+------------+------------+------------+
| Sales      | 2         | 6600.00    | 6200       | 7000       |
+------------+-----------+------------+------------+------------+

SELECT 
    department,
    COUNT(*) AS high_earners,
    AVG(salary) AS avg_high_salary
FROM employees
WHERE salary > 5000           -- ← تصفية الصفوف أولاً
GROUP BY department
HAVING COUNT(*) > 1;          -- ← ثم تصفية المجموعات

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

SELECT 
    product_name,
    COUNT(*) AS orders_count,
    SUM(amount) AS total_revenue
FROM orders
WHERE YEAR(order_date) = 2024        -- WHERE: تصفية السنة
GROUP BY product_name
HAVING SUM(amount) > 5000            -- HAVING: تصفية الإيرادات
ORDER BY total_revenue DESC;

-- الأقسام بأكثر من موظفين ومتوسط راتب > 5500
SELECT 
    department,
    COUNT(*) AS employees,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 2 AND AVG(salary) > 5500;

-- الأقسام بأكثر من 3 موظفين أو إجمالي رواتب > 15000
SELECT 
    department,
    COUNT(*) AS employees,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING COUNT(*) > 3 OR SUM(salary) > 15000;

SELECT 
    department,
    COUNT(*) AS employees,
    AVG(salary) AS avg_salary,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY department
HAVING 
    AVG(salary) BETWEEN 5500 AND 7000
    AND COUNT(*) >= 2
    AND MAX(salary) - MIN(salary) < 2000;

SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS monthly_revenue,
    AVG(amount) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY YEAR(order_date), MONTH(order_date)
HAVING SUM(amount) > 50000
ORDER BY year DESC, month DESC;

SELECT 
    customer_name,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_spent,
    AVG(amount) AS avg_order_value,
    MAX(order_date) AS last_order
FROM orders
GROUP BY customer_name
HAVING 
    COUNT(*) > 10 
    AND SUM(amount) > 5000
ORDER BY total_spent DESC
LIMIT 20;

SELECT 
    class,
    COUNT(*) AS total_students,
    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,
    ROUND(COUNT(CASE WHEN score >= 50 THEN 1 END) * 100.0 / COUNT(*), 2) AS pass_rate
FROM exam_results
GROUP BY class
HAVING 
    AVG(score) > 75 
    AND COUNT(CASE WHEN score >= 50 THEN 1 END) * 100.0 / COUNT(*) > 80
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
WHERE stock > 0
GROUP BY category
HAVING 
    SUM(stock * price) > 10000
    AND COUNT(*) >= 5
ORDER BY inventory_value DESC;

SELECT 
    department,
    COUNT(*) AS employees,
    SUM(salary) AS monthly_payroll,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(salary) - MIN(salary) AS salary_gap
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING 
    COUNT(*) >= 3
    AND SUM(salary) > 15000
    AND AVG(salary) > 5000
ORDER BY monthly_payroll DESC;

-- ❌ خطأ: HAVING بدون GROUP BY
SELECT AVG(salary)
FROM employees
HAVING AVG(salary) > 6000;

-- ERROR: HAVING clause requires GROUP BY

-- ✅ صحيح: إضافة GROUP BY
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

-- أو استخدم WHERE إذا لم تحتج GROUP BY
SELECT AVG(salary) AS avg_salary
FROM employees
WHERE salary > 6000;

-- ❌ خطأ: لا يمكن استخدام AVG في WHERE
SELECT department, 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;

-- سيء: تصفية كل شيء بعد التجميع
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING department != 'Temp';

-- جيد: تصفية قبل التجميع (أسرع)
SELECT department, AVG(salary)
FROM employees
WHERE department != 'Temp'
GROUP BY department;

-- تقرير: الأقسام الكبيرة والمكلفة
-- الشروط: أكثر من 3 موظفين وإجمالي رواتب > 15000
SELECT 
    department,
    COUNT(*) AS employees,
    SUM(salary) AS total_payroll
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 3 AND SUM(salary) > 15000;