Group By with In-Built Functions Examples

We can perform Group By clause with SQL in-build functions. Please see the examples below:

1. Grouping and Counting

Assume you have a table named employees with columns department and salary.

Count the number of employees in each department:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

2. Grouping and Summing

Calculate the total salary for each department:

SELECT department, SUM(salary)
FROM employees
GROUP BY department;

3. Grouping and Averaging

Calculate the average salary for each department:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

4. Grouping with Multiple Columns

Assume you also have a column job_title. Group by both department and job_title to get the count of employees for each job title within each department:

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

5. Using HAVING with GROUP BY

The HAVING clause is used to filter groups based on a condition, similar to the WHERE clause but applied to groups.

Get departments with more than 10 employees:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

6. Grouping and Finding Maximum/Minimum

Find the maximum salary in each department:

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

Find the minimum salary in each department:

SELECT department, MIN(salary)
FROM employees
GROUP BY department;