GROUP BY and HAVING Clauses

Estimated reading: 2 minutes 14 views

1. What is the purpose of the GROUP BY clause in SQL? Provide an example.

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions to perform calculations on each group.
Example:

				
					SELECT department_id, COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id;

2. Write a query to find the total number of employees in each department.

You can use GROUP BY along with the COUNT aggregate function to find the total number of employees in each department.
Example:

				
					SELECT department_id, COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id;

3. What is the difference between WHERE and HAVING clauses? Provide examples.

WHERE: Filters rows before any grouping or aggregation is applied.
HAVING: Filters rows after grouping or aggregation is performed.

Examples:

WHERE: Filters rows based on conditions before aggregation.

				
					SELECT department_id, salary 
FROM employees 
WHERE salary > 50000;

HAVING: Filters groups after aggregation.

				
					SELECT department_id, COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;

4. Write a query to find departments with more than 5 employees.

You can use GROUP BY and HAVING to find departments with more than 5 employees.
Example:

				
					SELECT department_id, COUNT(*) AS total_employees 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;

5. Can you use aggregate functions (SUM, AVG, etc.) without GROUP BY? Explain.

Yes, aggregate functions like SUM, AVG, COUNT, etc., can be used without GROUP BY to perform calculations on all rows in the table. This returns a single result, representing the entire dataset.
Example:

				
					SELECT SUM(salary) AS total_salary 
FROM employees;