SQL Group by keyword
used to categorize and group items under separate bucket and then you can use aggregate function to calculate the total items in each group
Given a list of countries and their corresponding population, calculate the total number of people in each continent
select Continent, sum(population) AS TotalPopulation
from Countries
group by Continent
Given a list of items and their corresponding prices, calculate the total sales prices under each category
select category , sum(price) as totalprice
from Sales
group by category
Given a list of employees and their corresponding departments, calculate the total salary of employees in each department
select department, sum(salary) as NoofEmployees
from Employees
group by department
Given a list of employees and their corresponding departments, calculate the number of employees in each department
select department, count(*) as NoofEmployees
from Employees
group by department
Having and Order by
Having is condition set to filter the rows that are generated from group by. It should always come after group by
Find all departments and their counts of employees from employees table where there are more than 1000 employees under it
select department, count(*) as NoofEmployees
from Employees
group by department
having count(*) > 1000
order by Department
The where clause comes before the group by
Find all departments and their counts of employees from employees table where there are more than 1000 employees under it and department name like sales
select department, count(*) as NoofEmployees
from Employees
group by department
where department like ‘Sales%’
having count(*) > 1000
order by Department