PostgreSQL-Database/C2/GROUP-BY-and-HAVING-Clause/English

Title of the Script: GROUP BY and HAVING Clause

Author: Ketki Bhamble / EduPyramids

Keywords: PostgreSQL, pgAdmin 4, query, GROUP BY clause, HAVING clause, aggregate functions, COUNT(*), EduPyramids, Video Tutorial.

Visual Cue	Narration
Slide 1 Title Slide	Welcome to this Spoken Tutorial on GROUP BY and HAVING Clause in PostgreSQL.
Slide 2 Learning Objectives	In this tutorial, we will learn how to Use the GROUP BY clause Apply aggregate functions with GROUP BY and Filter grouped results using the HAVING clause.
Slide 3 System Requirements Ubuntu 24.04 LTS PostgreSQL version 18.1, and PgAdmin 4 version 9.11.	To record this tutorial, I am using the following setup.
Slide 4 Pre-requisites https://EduPyramids.org	To follow this tutorial, Learners should have PostgreSQL and pgAdmin4. For the prerequisite PostgreSQL tutorials, please visit this website.
Slide 5 Code Files The following code file is required to practice this tutorial: gh-commands.txt This file is provided in the Code Files link on this tutorial page.	The following code file is required to practice this tutorial. This file is provided in the Code Files link of this tutorial page. Please download and extract the file.
	Let us get started. I have opened the pgAdmin4 interface and connected to the localhost server.
Display the students table on the screen SELECT * FROM students;	We are using the students table. It contains student ID, name, city, gender, date of birth, and CGPA.
Slide 6 GROUP BY	The GROUP BY clause groups rows that have the same values into summary rows. It is used along with aggregate functions to organise results.
Slide 7 Syntax of GROUP BY SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;	This is the syntax of the GROUP BY clause. GROUP BY clause groups rows with the same column values.
	Let us find the number of students from each city.
Type: SELECT city, COUNT(*) FROM students GROUP BY city; Click the Execute query icon. Point to the results.	Type the following query. It groups rows by city and counts the total number of students in each city. Let us execute the query. The result shows each city with the total number of students from that city.
	Now let us calculate the average CGPA of students in each city.
Type: SELECT city, ROUND(AVG(cgpa), 2) FROM students GROUP BY city; Click the Execute query icon.	Type the following query. This query groups students by city and calculates the average CGPA for each group.Let us execute this query.
	We can also group records using other columns such as gender.
Type: SELECT gender, COUNT(*) FROM students GROUP BY gender; Click the Execute query icon. Point to the results in the table.	Type this query. This query groups students based on gender. Let us execute the query. The result shows the count of male and female students in the table.
Slide 8 HAVING clause	The HAVING clause filters grouped results. It is used along with the GROUP BY clause and aggregate functions.
Type: SELECT city, COUNT(*) FROM students GROUP BY city HAVING COUNT(*) > 2; Click the Execute query icon. Point to the results in the table.	Type the following query. This query groups the records by city. Let us execute the query. It displays only those cities where the number of students is greater than 2.
Type: SELECT city, ROUND(AVG(cgpa), 2) FROM students GROUP BY city HAVING ROUND(AVG(cgpa), 2) > 8.5; Click the Execute query icon. Point to the results in the table.	Type the following query. Here the HAVING clause filters the grouped results. Let us execute the query. It displays only those cities where the average CGPA is greater than 8 point 5.
Type: SELECT city, COUNT(*) FROM students WHERE cgpa > 8 GROUP BY city HAVING COUNT(*) > 1; Click the Execute query icon. Point to the results in the table.	Let us type this query. In this query, we will analyze student data city-wise. First, we filter students with CGPA greater than 8. Then, we group these students by city. The WHERE clause filters rows before grouping. However the HAVING clause filters groups after aggregation. Then we will count students in each city. Let us execute the query. Output displays only those cities with more than one student.
	With this we come to the end of this tutorial.
Slide 9 Summary In this tutorial, we learnt how to: Use the GROUP BY clause, Apply aggregate functions with GROUP BY, and Filter grouped results using the HAVING clause.	Let us summarize.
Slide 10 + 11 Assignment As an Assignment, Find the maximum CGPA obtained by students in each city. Display the minimum CGPA for each gender. Find the total CGPA of students for each city. Display cities where the average CGPA is less than 8.5. Find genders that have more than 5 students. Display cities where the maximum CGPA is greater than 9.	We encourage you to do this assignment.
Slide 12 Thank you This Spoken Tutorial is brought to you by EduPyramids Educational Private Limited. SINE IIT Bombay. Thank you	Thank you for joining.

Contributors and Content Editors

ANJALISATDIVE, Madhurig