In this video we are discussing the concept of GROUPBY, where and having clause in SQL.

Below is the code used in the video.

CREATE TABLE studentmarks (
class VARCHAR(10),
subject VARCHAR(50),
student VARCHAR(50),
marks INT
)

insert into studentmarks (class, subject, student, marks)
values
('Class1', 'Maths', 'Student1', 63),
('Class1', 'Maths', 'Student2', 54),
('Class1', 'Science', 'Student1', 85),
('Class1', 'Science', 'Student2', 76),
('Class1', 'English', 'Student1', 78),
('Class1', 'English', 'Student2', 66),
('Class2', 'Maths', 'Student3', 85),
('Class2', 'Maths', 'Student4', 92),
('Class2', 'Science', 'Student3', 60),
('Class2', 'English', 'Student4', 83),
('Class2', 'English', 'Student3', 89),
('Class3', 'Maths', 'Student5', 56),
('Class3', 'Maths', 'Student6', 78),
('Class3', 'Science', 'Student7', 88),
('Class3', 'Science', 'Student5', 84),
('Class3', 'English', 'Student6', 67),
('Class3', 'English', 'Student7', 87);

select * from studentmarks;

-- use of where clause - filter rows based on some condition

select * from studentmarks where marks 80

select * from studentmarks where class = 'Class1'

select * from studentmarks where student = 'Student1'

select * from studentmarks where student = 'Student1' and subject = 'Maths'

-- use of group by
-- let's say the management wants to check the overall score in each class or subject or each student to see their strengths and weakness
-- whenever we use aggregate functions like sum, min, max, avg we use group by - as logically we can do aggregate based on some group only.

-- overall marks scored in each class - to see the class which is performing really well
select class, sum(marks) as total_marks
from studentmarks
group by class
order by total_marks

-- overall marks scored by each student - to see the student who is performing really well
select student, sum(marks) as total_marks
from studentmarks
group by student
-- but in above student may have different no of subjects, so average marks will be a better criteria to judge the performance

-- avg marks scored by each student

select student, avg(marks) as avg_marks
from studentmarks
group by student
order by avg(marks) desc

-- overall marks scored in each subject - to see in which subject students are performing well
-- again there may be different no of students in each subject so insted of sum, average will be a better criteria
select subject, avg(marks) as avg_marks
from studentmarks
group by subject order by avg_marks

-- score in each class and the respective subjects
select class, subject, avg(marks) as avg_marks
from studentmarks
group by class, subject
order by avg_marks desc

-- having is used to filter the rows acc to the aggregate functions. It is always used with group by clause only.
-- find the classes where overall marks of students is greater than 450
select class, sum(marks) as total_marks
from studentmarks
group by class
having sum(marks) = 450

-- find the subjects where overall marks of students is greater than 400
select subject, sum(marks) as total_marks
from studentmarks
group by subject
having sum(marks) = 400

-- find students where avg marks is less than 75
select student, avg(marks) as avg_marks
from studentmarks
group by student
having avg(marks) 75