WikiGalaxy
SQL Group By
Introduction to SQL Group By
Understanding Group By:
The SQL GROUP BY clause is used to arrange identical data into groups. This clause is often used with aggregate functions such as COUNT, SUM, AVG, MAX, or MIN to perform operations on each group of data.
Basic Syntax:
The basic syntax for using GROUP BY is: SELECT column_name(s), aggregate_function(column_name) FROM table_name GROUP BY column_name(s);
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department;
Example Explanation:
This example counts the number of employees in each department. The GROUP BY clause groups the rows by department before applying the COUNT function.
Console Output:
Sales: 10
HR: 5
IT: 8
Using GROUP BY with Multiple Columns
Combining Columns:
You can use GROUP BY with multiple columns to group data based on more than one column, providing a finer granularity of grouping.
SELECT department, role, COUNT(employee_id)
FROM employees
GROUP BY department, role;
Example Explanation:
This query groups the result set by both department and role, allowing you to count employees for each unique department-role combination.
Console Output:
Sales, Manager: 3
Sales, Executive: 7
HR, Recruiter: 5
IT, Developer: 8
GROUP BY with HAVING Clause
Filtering Groups:
The HAVING clause is used to filter groups based on a condition, similar to the WHERE clause but used for groups.
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 5;
Example Explanation:
This query selects only those departments where the number of employees is greater than 5.
Console Output:
Sales: 10
IT: 8
GROUP BY with Aggregate Functions
Using Aggregates:
Aggregate functions like SUM, AVG, MIN, and MAX are often used with GROUP BY to perform calculations on each group.
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
Example Explanation:
This query calculates the total salary paid to employees in each department.
Console Output:
Sales: $50000
HR: $25000
IT: $40000
GROUP BY with ORDER BY
Ordering Results:
The ORDER BY clause can be used in conjunction with GROUP BY to sort the grouped results.
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
ORDER BY COUNT(employee_id) DESC;
Example Explanation:
This query orders the departments by the number of employees, from highest to lowest.
Console Output:
Sales: 10
IT: 8
HR: 5
GROUP BY with JOIN
Combining Tables:
You can use GROUP BY in queries that involve joining multiple tables, allowing for complex data aggregation.
SELECT e.department, COUNT(p.project_id)
FROM employees e
JOIN projects p ON e.employee_id = p.employee_id
GROUP BY e.department;
Example Explanation:
This query counts the number of projects each department is handling by joining the employees and projects tables and grouping by department.
Console Output:
Sales: 15
HR: 7
IT: 12
GROUP BY with Subqueries
Using Subqueries:
Subqueries can be used within a GROUP BY clause to provide more detailed analysis and results.
SELECT department, AVG(salary)
FROM (
SELECT department, salary
FROM employees
WHERE salary > 30000
) AS high_salary_employees
GROUP BY department;
Example Explanation:
This query calculates the average salary for employees earning more than $30,000, grouped by department.
Console Output:
Sales: $35000
HR: $32000
IT: $37000
GROUP BY with CASE Statement
Conditional Grouping:
The CASE statement can be used within a GROUP BY to create conditional groupings based on specific conditions.
SELECT
CASE
WHEN salary > 40000 THEN 'High'
WHEN salary BETWEEN 30000 AND 40000 THEN 'Medium'
ELSE 'Low'
END AS salary_range, COUNT(employee_id)
FROM employees
GROUP BY salary_range;
Example Explanation:
This query categorizes employees into salary ranges and counts how many employees fall into each range.
Console Output:
High: 5
Medium: 10
Low: 7
