View source for 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.


{| border=1
|- 
| align=center| '''Visual Cue'''
| align=center| '''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/ 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. 

Please download and extract the file.

|| 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.
|-
|}