The SQL GROUP BY clause is used to arrange identical data into groups
based on one or more columns. It is commonly used with aggregate
functions like COUNT(), SUM(), AVG(), MAX() and MIN() to perform
calculations on each group of data.
Example: First, we will create a demo SQL database and table, on which
we will use the GROUP BY command.
Query:
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;
Output:
Groups all employee records based on their department.
● Calculates and returns total salary for each department.
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2;
● aggregate_function: function used for aggregation, e.g., SUM(),
AVG(), COUNT().
● table_name: name of the table from which data is selected.
● condition: Optional condition to filter rows before grouping (used
with WHERE).
● column1, column2: Columns on which the grouping is applied.
Working with GROUP BY
Let's assume that we have a Student table. We will insert some sample
data into this table and then perform operations using GROUP BY to
understand how it groups rows based on a column and aggregates data.
Student Table
Example 1: Group By Single Column
When we group by a single column, rows with the same value in that
column are combined. For example, grouping by subject shows how many
students are enrolled in each subject.
Query:
SELECT subject, COUNT(*) AS Student_Count
FROM Student
GROUP BY subject;
Output:
● Groups all student records by their subject.
● Counts total number of students in each subject.
Example 2: Group By Multiple Columns
Using GROUP BY with multiple columns groups rows that share the same
values in those columns. For example, grouping by subject and year will
combine rows with the same subject–year pair and we can count how
many students fall into each group.
Query:
SELECT subject, year, COUNT(*)
FROM Student
GROUP BY subject, year;
Output:
● Students with the same subject and year are grouped together.
● Since each subject and year pair occurs twice, the count is 2 for
every group.
HAVING Clause in GROUP BY Clause
HAVING clause is used to filter results after grouping, especially when
working with aggregate functions like SUM(), COUNT() or AVG(). Unlike
WHERE, it applies conditions on grouped data.
Employees Table
Example 1: Filter by Total Salary
In this query, we group employees by age and display only those whose
total salary is greater than 50,000.
SELECT age, SUM(sal) FROM Employees
GROUP BY age
HAVING SUM(sal)>50000;
Output:
● Groups employees by age and sums their salaries.
● Returns only ages where total salary exceeds 50000.
Example 2: Filter by Average Salary
In this query, we group employees by age and display only those age
groups where average salary is above 60,000.
SELECT age, AVG(sal) AS Average_Salary
FROM Employees
GROUP BY age
HAVING AVG(sal) > 60000;
Output:
● Groups employees by age and calculates average salary.
● Filters only age groups with average salary above 60000.
SQL Joins are used to combine data from two or more tables based on a
related column. They help in:
● Retrieving connected data stored across multiple tables.
● Matching records using common columns.
● Improving data analysis by combining related information.
● Creating meaningful result sets from separate tables.
Types of SQL Joins
SQL joins are categorized into different types based on how rows from
two tables are matched and combined.
1. INNER JOIN
INNER JOIN is used to retrieve rows where matching values exist in both
[Link] helps in:
● Combining records based on a related column.
● Returning only matching rows from both tables.
● Excluding non-matching data from the result set.
● Ensuring accurate data relationships between tables.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,.... FROM
table1 INNER JOIN
table2 ON table1.matching_column = table2.matching_column;
Inner join
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as
INNER JOIN.
Example of INNER JOIN:
Consider the two tables, Student and StudentCourse, which share a
common column ROLL_NO. Using SQL JOINS, we can combine data from
these tables based on their relationship, allowing us to retrieve
meaningful information like student details along with their enrolled
courses.
Student Table:
StudentCourse Table:
Let's look at the example of INNER JOIN clause, and understand it's
working. This query will show the names and age of students enrolled in
different courses.
Query:
SELECT StudentCourse.COURSE_ID, [Link], [Link]
FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:
2. LEFT JOIN
LEFT JOIN is used to retrieve all rows from the left table and matching rows
from the right [Link] helps in:
● Returning all records from the left table.
● Showing matching data from the right table.
● Displaying NULL values where no match exists in the right table.
● Performing outer joins, also known as LEFT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Left Join
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are
the same.
Example: In this example, the LEFT JOIN retrieves all rows from the
Student table and the matching rows from the StudentCourse table based
on the ROLL_NO column.
Query:
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
3. RIGHT JOIN
RIGHT JOIN is used to retrieve all rows from the right table and the
matching rows from the left [Link] helps in:
● Returning all records from the right-side table.
● Showing matching data from the left-side table.
● Displaying NULL values where no match exists in the left table.
● Performing outer joins, also known as RIGHT OUTER JOIN.
Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Right Join
Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both
are the same
Example: In this example, the RIGHT JOIN retrieves all rows from the
StudentCourse table and the matching rows from the Student table based
on the ROLL_NO column.
Query:
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
4. FULL JOIN
FULL JOIN is used to combine the results of both LEFT JOIN and RIGHT
JOIN. It helps in:
● Returning all rows from both tables.
● Showing matching records from each table.
● Displaying NULL values where no match exists in either table.
● Providing complete data from both sides of the join.
Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Full Join
Example: This example uses a FULL JOIN to return all rows from both
tables. Matching records appear together, while non-matching records
still show up with NULL values for the missing fields.
Query:
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output :
5. Natural Join
A Natural Join is a type of INNER JOIN that automatically joins two tables
based on columns with the same name and data type. It returns only the
rows where the values in the common columns match.
● It joins tables using common columns with the same name.
● It returns only rows where values in those columns match.
● The common column appears only once in the result.
Example: Look at the two tables below:
Employee Table:
Department Table:
Example: Find all Employees and their respective departments.
SELECT
Emp_name,
Dept_name
FROM Employee
NATURAL JOIN Department;
Output:
The SQL HAVING clause filters the results of grouped data after using the
GROUP BY clause. It is used with aggregate functions such as SUM(),
COUNT(), or AVG() to display only those groups that meet specific
conditions.
Example: First, we will create a demo SQL database and table, on which
we will use the HAVING Clause command.
Query:
SELECT Department, COUNT(EmpID) AS Employee_Count
FROM Employees
GROUP BY Department
HAVING COUNT(EmpID) > 1;
Output:
Syntax:
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
Note: When HAVING is used without GROUP BY, the entire table is
treated as a single group. This works only with aggregate functions.
Examples of HAVING Clause
First, we create the Employee table and insert sample data to
demonstrate the HAVING clause.
Employee Table
Example 1: Filter Total Salary
In this example, we calculate the total salary of all employees and display
it only if it meets the specified condition.
Query:
SELECT SUM(Salary) AS Total_Salary
FROM Employee
GROUP BY (SELECT 1)
HAVING SUM(Salary) >= 250000;
Output:
● SUM(Salary) adds up salaries of all employees.
● GROUP BY (SELECT 1) treats the entire table as one single
group.
● HAVING SUM(Salary) >= 250000 filters the result and shows it
only when the condition is met.
Note: GROUP BY (SELECT 1) is database-specific and may not work in all
SQL databases. So alternatively, you can omit GROUP BY and use
HAVING directly with aggregate functions.
Example 2: Filter Average Salary
In this example, we calculate the average salary of all employees and
display it only if the average exceeds 55,000.
Query:
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 55000;
Output:
● GROUP BY Department calculates average salary per
department.
● AVG(Salary) finds the department-wise average.
● HAVING AVG(Salary) > 55000 shows only departments whose
average salary exceeds 55,000.
Example 3: Filter Maximum Salary
In this example, we find the highest salary among employees and display
it only if it exceeds 70,000.
Query:
SELECT MAX(Salary) AS Max_Salary
FROM Employee
HAVING MAX(Salary) > 70000;
Output
Example 4: Filter Minimum Experience
In this example, we find the least experienced employee and display it
only if their experience is less than 3 years.
Query:
SELECT MIN(Experience) AS Min_Experience
FROM Employee
HAVING MIN(Experience) < 3;
Output
Example 5: Multiple Conditions
In this example, we calculate both the total and average salary of
employees and display the results only if the total salary is at least
250,000 and the average salary exceeds 55,000.
Query:
SELECT SUM(Salary) AS Total_Salary, AVG(Salary) AS
Average_Salary
FROM Employee
HAVING SUM(Salary) >= 250000 AND AVG(Salary) > 55000;
Output: