In this tutorial, we learn GROUP BY Clause with the help of given aggregate
functions
1. Count()
2. Sum()
3. avg()
4. Min()
5. Max()
GROUP By Clause is used to arrange identical data into groups with the help of aggregate function means if a column has the same values in multiple rows then it will convert these rows in a group.
Syntax:-
1. Count()
2. Sum()
3. avg()
4. Min()
5. Max()
GROUP By Clause is used to arrange identical data into groups with the help of aggregate function means if a column has the same values in multiple rows then it will convert these rows in a group.
Syntax:-
SELECT ColumnName1, aggrigate_function(ColumnName2)
FROM table_name
WHERE Condition
GROUP BY ColumnName1, ColumnName2
ORDER BY ColumnName1, ColumnName2;
Let's create a table and insert some data to use GROUP BY Clause
CREATE TABLE Employee
(
ID INT PRIMARY KEY,
Name NVARCHAR(50),
Age INT,
Gender VARCHAR(10),
Salary INT,
City NVARCHAR(30)
)
GO
INSERT INTO Employee(ID, Name, Age, Gender, Salary, City)
SELECT 1,'Kapil',23,'Male',25000,'Delhi' UNION
SELECT 2,'Vipin',32,'Male',55000,'Kanpur' UNION
SELECT 3,'Shyam',29,'Male',50000,'Delhi' UNION
SELECT 4,'Dolly',30,'Female',52000,'Kanpur' UNION
SELECT 5,'Geeta',25,'Female',70000,'Delhi' UNION
SELECT 6,'Harish',36,'Male',85000,'UP' UNION
SELECT 7,'Pawan',36,'Male',70000,'UP'
Group By single column: Write a query to find out city-wise total
salary as given in the below image
SELECT City,SUM(Salary) TotalSalary
FROM Employee
GROUP BY City
As per the output, the records with duplicate City are grouped under the same City column and their corresponding TotalSalary is the sum of the SALARY of duplicate rows. The SUM() aggregate function is used here to calculate the sum
of salary.
GROUP BY Multiple columns: Write a query to find out city-wise total
salary and number of employees as given in the below image
SELECT City,SUM(Salary) TotalSalary,COUNT(ID) NumberOfEmployees
FROM Employee
GROUP BY City
The only difference here is that we have added another aggregate function Count() to count the number of employees.
Apply Filter:- Write a query to find out the number of employees in 'Delhi' city. We can write this query in two ways. Both will produce the same output as given in the below image
1. Using WHERE Clause
SELECT City,COUNT(ID) NumberOfEmployees
FROM Employee
WHERE City='Delhi'
GROUP BY City
Note: WHERE Clause always take place before GROUP BY Clause
2. Using HAVING Clause
SELECT City,COUNT(ID) NumberOfEmployees
FROM Employee
GROUP BY City
HAVING City='Delhi'
Note: HAVING Clause always take place after GROUP BY Clause
Difference between Where clause and GROUP By clause1. WHERE Clause take place before GROUP BY Clause, which means WHERE clause apply filters before performing aggregate calculations. HAVING Clause take place after GROUP BY Clause, which means the HAVING clause apply filters after performing aggregate calculations. So if terms of efficiency, HAVING Clause is slower than WHERE Clause.
2. The WHERE clause first apply filter as per specified condition and then retrive data. Where as, the HAVING clause first fetches all data, and then apply filter as per specified condition so we can say WHERE clause is a pre-filter, whereas HAVING clause is a post-filter.
3. We can use WHERE clause with Select, Insert, and Update statements, on the other hand HAVING clause can only be used with the Select statement.
0 Comments