MySQL: شرح مفصل لدوال التجميع في MySQL وعبارة GROUP BY

SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Count the total number of lines
SELECT COUNT(*) FROM employees;

-- Count non-NULL rows
SELECT COUNT(email) FROM employees;

-- Count the number of unique entries
SELECT COUNT(DISTINCT department) FROM employees;

-- Total Wages
SELECT SUM(salary) FROM employees;

-- Average Wage
SELECT AVG(salary) FROM employees;

-- Conditional Summation
SELECT SUM(amount) FROM orders WHERE status = 'paid';

-- Highest Salary
SELECT MAX(salary) FROM employees;

-- Minimum Wage
SELECT MIN(salary) FROM employees;

-- Earliest/Latest Date
SELECT MIN(hire_date) AS earliest, MAX(hire_date) AS latest FROM employees;

-- By Department
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

+-------------+-----------+-----------+
| department  | emp_count | avg_salary|
+-------------+-----------+-----------+
| Engineering |        15 |   8500.00 |
| Sales       |        10 |   6000.00 |
| Marketing   |         8 |   5500.00 |
| HR          |         5 |   5000.00 |
+-------------+-----------+-----------+

-- By department and job title
SELECT 
    department,
    job_title,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;

-- Orders by Year
SELECT 
    YEAR(order_date) AS year,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY YEAR(order_date);

-- Statistics by Age Group
SELECT 
    CASE 
        WHEN age < 18 THEN 'Under 18'
        WHEN age BETWEEN 18 AND 30 THEN '18-30'
        WHEN age BETWEEN 31 AND 50 THEN '31-50'
        ELSE 'Over 50'
    END AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY age_group;

-- Find the average wage > 6000 the department
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 6000;

-- Find the number of orders > 10 's customers
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING order_count > 10;

-- WHERE + GROUP BY + HAVING Combination
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'  -- Filter the rows first
GROUP BY department               -- Regroup
HAVING avg_salary > 6000;        -- Final Filter Group

-- Automatically generate total rows
SELECT 
    department,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;

+-------------+-----------+--------------+
| department  | emp_count | total_salary |
+-------------+-----------+--------------+
| Engineering |        15 |    127500.00 |
| Sales       |        10 |     60000.00 |
| Marketing   |         8 |     44000.00 |
| HR          |         5 |     25000.00 |
| NULL        |        38 |    256500.00 |  -- Total
+-------------+-----------+--------------+

-- COUNT(*) vs COUNT(email)
SELECT 
    COUNT(*) AS total_rows,
    COUNT(email) AS has_email,
    COUNT(*) - COUNT(email) AS no_email
FROM users;

-- AVG Ignore NULL
SELECT AVG(salary) FROM employees;  -- NULL Not included in the calculation