Group by Clause

---

Group by clause is used to group rows into a set of summary rows by the values of one or more columns

It is always used in conjuction with one or more aggregiate functions,these functions are COUNT(),MAX(),MIN(),SUM(),AVG()
Lets us understand by an example

Example 1-> Write an SQL statment to print the print total salary of employees on the basis of cities.Use Employeetbl table

First,we will create a table named as Employeetbl with column named as ID,Name,Gender,Salary,City and then insert a few records in it

CREATION

create table Employeetbl
( ID int not null,
Name varchar(20) not null,
Gender varchar(10) not null,
Salary int not null,
City varchar(10) not null
);

                    

copy

INSERTION

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(1,'Tom','Male',4000,'London');

                    

copy

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(2,'Pam','Female',3000,'New York');
                    

copy

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(3,'John','Male',3500,'London');
                    

copy

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(4,'Sam','Male',5000,'London');
                    

copy

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(5,'James','Male',6500,'Sydeny');
                    

copy

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(6,'Sara','Female',8800,'New York');
                    

copy

insert into Employeetbl(ID,Name,Gender,Salary,City)
Values(7,'Ava','Female',5500,'London');

                    

copy

GROUP BY clause

select City,SUM(Salary) as TotalSal
from Employeetbl	
GROUP BY City;
                    

copy

If we remove group by clause and the execute the SQL statement,it will give error as:

'Employeetbl.City' is invalid in the select list because it is not contained in either an aggregiate function or the group clause

This error occurs because the city column is neither in an aggregiate function nor in the group by clause

The column which we are using in the select list must be in aggregiate function or it should be part of "GROUP BY" Clause.

Example 2-> Write an SQL statment to print the customer id along with the maximum amount spend from sales table.

(Click on this link for insertion and creation of sales table)

Select CustomerID,MAX(Amount) as [MAX AMOUNT]
from Sales
GROUP BY CustomerID;
                    

copy

GROUP clause-multiple columns

---

We can also perform group by on multiple columns.Let us understand this by an example

Example- Write an SQL statment to print the print total salary by city and by gender of employees.Use Employeetbl table

select City,Gender,SUM(Salary) as TotalSal
from Employeetbl	
GROUP BY City,Gender;
                    

copy

Here are we are grouping by multiple columns,i.e first by City and then by gender

Filtering columns using WHERE and HAVING clause

---

The most flexible and common way to filter any record is by using WHERE clause.This WHERE Clause is used to filter only those records which fulfill a specified condition

But in SQL HAVING clause is added because the WHERE keyword cannot be used with aggregiate function.This HAVING clause is often used with GROUP BY clause to filter groups on a specified condition.

Let us see the example of both HAVING and WHERE Clause

Example-> Write an SQL statment to print the print total salary of employees on the basis of cities where city should be "New York"

Using WHERE clause

select City,SUM(Salary) as TotalSal
from Employeetbl	
Where City= "New York"
GROUP BY City;
                    

copy

Using HAVING clause

select City,SUM(Salary) as TotalSal
from Employeetbl
GROUP BY City	
Having City= "New York"
                    

copy

Since both WHERE and HAVING produce the same result.But on the basis of working,WHERE clause is used to filter rows before aggregation,where as HAVING clause is to filter groups after aggregations

JOINS - MySQL

---

JOINS in SQL are used to fetch data from 2 or more related tables.These related tables are related to each other using foreign key constraints

In SQL, There are different types JOINS:

1. INNER JOIN
2. CROSS JOIN

Let us discuss each JOIN types,with example given below:

First we will create table Department with column named as ID,DepartmentName,Location,DepartmentHead and then insert a few records in it.

Creation

Create table Department
(
     ID int not null,
     DepartmentName varchar(50),
     Location varchar(50),
     DepartmentHead varchar(50),
     primary key(id)
);

                    

copy

Insertion

Insert into Department 
values (1, 'IT', 'London', 'Rick');
                    

copy

Insert into Department 
values (2, 'Payroll', 'Delhi', 'Ron');
                    

copy

Insert into Department 
values (3, 'HR', 'New York', 'Christie');
                    

copy

Insert into Department 
values (4, 'Other Department', 'Sydney', 'Cindrella');
                    

copy

Then Create Employee table with column named as ID,Name,Gender,Salary,DepartmentId and then insert a few records in it

Creation

Create table Employee
(
     ID int primary key,
     Name varchar(50),
     Gender varchar(50),
     Salary int,
     DepartmentId int,
     foreign key (DepartmentId) references Department(ID)
);
                    

copy

Insertion

Insert into Employee 
values (1, 'Tom', 'Male', 4000, 1);
                    

copy

Insert into Employee 
values (2, 'Pam', 'Female', 3000, 3);
                    

copy

Insert into Employee 
values (3, 'John', 'Male', 3500, 1);
                    

copy

Insert into Employee 
values (4, 'Sam', 'Male', 4500, 2);
                    

copy

Insert into Employee values (5, 'Todd', 'Male', 2800, 2);
                    

copy

Insert into Employee 
values (6, 'Ben', 'Male', 7000, 1);
                    

copy

Insert into Employee 
values (8, 'Valarie', 'Female', 5500, 1);
                    

copy

 

Insert into Employee 
values (9, 'James', 'Male', 6500, NULL);
                    

copy

 

Insert into Employee 
values (10, 'Russell', 'Male', 8800, NULL);
                    

copy

INNER JOIN

---

INNER Join returns only the matching records from both the tables,non matching ones are eliminated

Let us understand this by an example:

Write a query to display Name,Gender,Salary and DepartmentName from Employee table and Department table using inner join

 

select NAME,Gender,Salary,DepartmentName
from Employee emp
INNER JOIN Department dep
ON emp.DepartmentId = dep.Id;
                    

copy

OUTPUT:



Here matching rows between two tables are retrieved,that is why "James" and "Russell" are not retrieved because these rows doesn't have a matching departmentID from Department table

CROSS JOIN

---

CROSS Join returns the Cartesian product of the 2 tables

Example:

Write a query to display the cartesian product of Name,Gender,Salary and DepartmentName from Employee and Department table

 

select NAME,Gender,Salary,DepartmentName
from Employee
cross JOIN Department;
                    

copy

OUTPUT: