PostgreSQL-Database/C2/Aggregate-Functions/English - Revision history

Madhurig at 09:43, 23 April 2026

2026-04-23T09:43:31Z

Madhurig at 07:29, 23 April 2026

2026-04-23T07:29:42Z

Madhurig at 07:20, 23 April 2026

2026-04-23T07:20:06Z

ANJALISATDIVE: Created page with " '''Title of the script: Aggregate Functions''' '''Authors:''' EduPyramids.''' '''Keywords:''' PostgreSQL, pgAdmin 4, Aggregate Functions, COUNT, AVG(average), SUM, MIN, MA..."

2026-04-23T06:41:53Z

Created page with " '''Title of the script: Aggregate Functions''' '''Authors:''' EduPyramids.''' '''Keywords:''' PostgreSQL, pgAdmin 4, Aggregate Functions, COUNT, AVG(average), SUM, MIN, MA..."

New page

'''Title of the script: Aggregate Functions'''

'''Authors:''' EduPyramids.'''

'''Keywords:''' PostgreSQL, pgAdmin 4, Aggregate Functions, COUNT, AVG(average), SUM, MIN, MAX, Null values, COUNT asterisk, ROUND value, EduPyramids, Video Tutorial.

{| border=1
|-
! width="35%" | '''Visual Cue'''
! width="65%" | '''Narration'''

|-
|| '''Title Slide'''
|| Welcome to this Spoken Tutorial on '''Aggregate Functions'''.

|-
|| '''Slide 2'''
'''Learning Objectives'''
|| In this tutorial, we will learn about
* '''Aggregate Functions'''

|-
|| '''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'''
|| To follow this tutorial,
* Learners should have '''Postgres Q L''' and '''p g Admin 4'''
* For the prerequisite '''Postgres Q L''' tutorials, please visit this website.

|-
|| '''Slide 5'''
'''Code Files'''
The following code file is required to practice this tutorial:
'''af-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.

|-
|| '''Slide 6'''
'''Aggregate Functions'''
|| '''Aggregate''' functions perform calculations on multiple rows and return a single value.
Some common aggregate functions are '''COUNT''', '''average''', '''SUM''', '''MIN''', and '''MAX'''.

|-
||
|| I have opened the '''p g Admin 4''' interface and connected to the localhost server.
Let us learn these functions using query examples.
This is the student table.
First, we will learn about the '''COUNT''' function.

|-
|| '''Type:'''
SELECT COUNT(*) FROM students;'''
'''Highlight COUNT(*)
Click on the Execute Query button.
|| Let us type the following query.
The '''COUNT''' asterisk function returns the total number of rows in the table.
It counts all records including those containing '''NULL''' values.
Let us execute the query.
This shows the total number of students stored in the table.

|-
||
|| Now we will see how '''COUNT''' works when applied to a specific column.

|-
|| '''Type:'''
'''SELECT COUNT(cgpa) FROM students;'''
'''Highlight:'''
'''COUNT(cgpa)'''
Click on the Execute Query button.
|| Type the query.
The '''COUNT''' function counts the number of not '''NULL''' values in the '''C G P A''' column.
Let us execute the query.

|-
|| '''Cursor on the output.'''
|| The output displays the number of students whose '''C G P A''' value is available.

|-
|| Show both the outputs in different query windows.
|| Here, '''COUNT asterisk''' returns all the rows.
However, the COUNT '''column''' counts only '''not NULL''' values of the specified column.

|-
||
|| Next, we will calculate the average C G P A.

|-
|| '''Type:'''
'''SELECT AVG(cgpa) FROM students;'''
|| Type the following query.

|-
|| '''Highlight:'''
'''AVG(cgpa)'''
|| The '''average''' function calculates the average of all cgpa values.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Let us execute the query.
The output displays the average c g p a of all students.

|-
|| Type this command:
'''SELECT ROUND(AVG(cgpa), 2)'''
'''FROM students;'''
Click on the execute button.
|| Observe that, using the average keyword we get insignificant digits after the decimal point.
To reduce the number of insignificant digits after the decimal point, type this query.
ROUND value 2 limits the result to 2 digits after the decimal point.
Execute the query.
The output displays only two digits after the decimal point.

|-
||
|| Now, let us find the highest C G P A.

|-
|| '''Type:'''
'''SELECT MAX(cgpa) FROM students;'''
'''Highlight:'''
MAX(cgpa)
|| Type the following query.
The '''MAX''' function returns the largest value in the C G P A column.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Execute the query.
The result displays the highest C G P A recorded in the students table.

|-
||
|| Next, we will find the lowest C G P A .

|-
|| '''Type:'''
'''SELECT MIN(cgpa) FROM students;'''
'''Highlight:'''
'''MIN(cgpa)'''
|| Type the following query.
'''MIN''' returns the smallest value in the column.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Execute the query.
The output displays the lowest C G P A recorded in the students table.

|-
||
|| Finally, we will calculate the total C G P A .

|-
|| '''Type:'''
'''SELECT SUM(cgpa) FROM students;'''
'''Highlight:'''
'''SUM(cgpa)'''
|| Type the following query.
The '''SUM''' function helps us find the total C G P A of all students.

|-
|| Click on the Execute Query button.
'''Cursor on the output.'''
|| Execute the query.
The output displays the sum of all CGPA values.

|-
||
|| With this, we come to the end of this tutorial.

|-
|| '''Slide 10'''
'''Summary'''
In this tutorial, we have learnt about
• '''Aggregate Functions'''
|| Let us summarise.

|-
|| '''Slide 11'''
'''Assignment'''
'''As an Assignment,'''
• Write a query to find how many different cities are present in the students table.
• Calculate the average CGPA of students born after 1996.
• Find the highest CGPA among female students.
• Find the lowest CGPA among male students.
• Calculate the total CGPA of students from each city.
|| We encourage you to do this assignment.

|-
|| '''Slide 12'''
This '''Spoken Tutorial''' is brought to you by '''EduPyramids''' Educational Private Limited SINE IIT Bombay.
'''Thank you'''
|| Thank you.

|}

@@ Line 71: / Line 71: @@
 '''Aggregate Functions'''
 || '''Aggregate''' functions perform calculations on multiple rows and return a single value.
-Some common aggregate functions are '''COUNT''', '''average''', '''SUM''', '''MIN''', and '''MAX'''.
+Some common aggregate functions are '''COUNT''', '''Average''', '''SUM''', '''MIN''', and '''MAX'''.
 |-

@@ Line 13: / Line 13: @@
 |-
-|| '''Title Slide'''
+|| '''Slide 1'''
 || Welcome to this Spoken Tutorial on '''Aggregate Functions'''.
 |-
 || '''Slide 2'''
 '''Learning Objectives'''
-|| In this tutorial, we will learn about
+|| In this tutorial, we will learn about:
 * '''Aggregate Functions'''
 |-
 || '''Slide 3'''
 '''System Requirements'''
-• '''Ubuntu 24.04 LTS'''
-• '''PostgreSQL version 18.1''', and
+* '''Ubuntu 24.04 LTS'''
-• '''PgAdmin 4 version 9.11'''.
+* '''PostgreSQL version 18.1''', and
 || To record this tutorial, I am using the following setup.
 |-
 || '''Slide 4'''
 '''Pre-requisites'''
 || To follow this tutorial,
 * Learners should have '''PostgreSQL''' and '''pgAdmin4'''
@@ Line 39: / Line 46: @@
 |-
 || '''Slide 5'''
 '''Code Files'''
 The following code file is required to practice this tutorial:
 '''af-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.
@@ Line 55: / Line 69: @@
 |-
 || '''Slide 6'''
 '''Aggregate Functions'''
 || '''Aggregate''' functions perform calculations on multiple rows and return a single value.
@@ Line 73: / Line 88: @@
 || '''Type:'''
 SELECT COUNT(*) FROM students;'''
 '''Highlight COUNT(*)
 Click on the Execute Query button.
 || Let us type the following query.
@@ Line 92: / Line 109: @@
 || '''Type:'''
 '''SELECT COUNT(cgpa) FROM students;'''
 '''Highlight:'''
 '''COUNT(cgpa)'''
 Click on the Execute Query button.
 || Let us type the following query.
@@ Line 127: / Line 146: @@
 |-
 || Click on the Execute Query button.
 '''Cursor on the output.'''
 || Let us execute the query.
@@ Line 134: / Line 154: @@
 |-
 || Type this command:
 '''SELECT ROUND(AVG(cgpa), 2)'''
 '''FROM students;'''
 Click on the execute button.
 || Observe that, using the average keyword we get insignificant digits after the decimal point.
@@ Line 154: / Line 176: @@
 || '''Type:'''
 '''SELECT MAX(cgpa) FROM students;'''
 '''Highlight:'''
 MAX(cgpa)
@@ Line 162: / Line 185: @@
 |-
 || Click on the Execute Query button.
 '''Cursor on the output.'''
 || Execute the query.
@@ Line 174: / Line 198: @@
 || '''Type:'''
 '''SELECT MIN(cgpa) FROM students;'''
 '''Highlight:'''
 '''MIN(cgpa)'''
@@ Line 182: / Line 207: @@
 |-
 || Click on the Execute Query button.
 '''Cursor on the output.'''
 || Execute the query.
@@ Line 194: / Line 220: @@
 || '''Type:'''
 '''SELECT SUM(cgpa) FROM students;'''
 '''Highlight:'''
 '''SUM(cgpa)'''
@@ Line 202: / Line 229: @@
 |-
 || Click on the Execute Query button.
 '''Cursor on the output.'''
 || Execute the query.
@@ Line 213: / Line 241: @@
 |-
 || '''Slide 10'''
 '''Summary'''
 In this tutorial, we have learnt about
-• '''Aggregate Functions'''
+* '''Aggregate Functions'''
 || Let us summarise.
 |-
 || '''Slide 11'''
 '''Assignment'''
 '''As an Assignment,'''
-• Write a query to find how many different cities are present in the students table.
-• Calculate the average CGPA of students born after 1996.
+* Write a query to find how many different cities are present in the students table.
-• Find the highest CGPA among female students.
+* Calculate the average CGPA of students born after 1996.
-• Find the lowest CGPA among male students.
+* Find the highest CGPA among female students.
-• Calculate the total CGPA of students from each city.
+* Find the lowest CGPA among male students.
 || We encourage you to do this assignment.
 |-
 || '''Slide 12'''
 This '''Spoken Tutorial''' is brought to you by '''EduPyramids''' Educational Private Limited SINE IIT Bombay.
 '''Thank you'''
 || Thank you.
 |}

@@ Line 34: / Line 34: @@
 '''Pre-requisites'''
 || To follow this tutorial,
-* Learners should have '''Postgres Q L''' and '''p g Admin 4'''
+* Learners should have '''PostgreSQL''' and '''pgAdmin4'''
-* For the prerequisite '''Postgres Q L''' tutorials, please visit this website.
+* For the prerequisite '''PostgreSQL''' tutorials, please visit this website.
 |-
@@ Line 44: / Line 44: @@
 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.
@@ Line 55: / Line 57: @@
 '''Aggregate Functions'''
 || '''Aggregate''' functions perform calculations on multiple rows and return a single value.
 Some common aggregate functions are '''COUNT''', '''average''', '''SUM''', '''MIN''', and '''MAX'''.
@@ Line 60: / Line 63: @@
 ||
 || I have opened the '''p g Admin 4''' interface and connected to the localhost server.
 Let us learn these functions using query examples.
 This is the student table.
 First, we will learn about the '''COUNT''' function.
@@ Line 70: / Line 76: @@
 Click on the Execute Query button.
 || Let us type the following query.
 The '''COUNT''' asterisk function returns the total number of rows in the table.
 It counts all records including those containing '''NULL''' values.
 Let us execute the query.
 This shows the total number of students stored in the table.
@@ Line 85: / Line 95: @@
 '''COUNT(cgpa)'''
 Click on the Execute Query button.
-|| Type the query.
+|| Let us type the following query.
 The '''COUNT''' function counts the number of not '''NULL''' values in the '''C G P A''' column.
 Let us execute the query.
@@ Line 96: / Line 108: @@
 || Show both the outputs in different query windows.
 || Here, '''COUNT asterisk''' returns all the rows.
 However, the COUNT '''column''' counts only '''not NULL''' values of the specified column.
@@ Line 116: / Line 129: @@
 '''Cursor on the output.'''
 || Let us execute the query.
 The output displays the average c g p a of all students.
@@ Line 124: / Line 138: @@
 Click on the execute button.
 || Observe that, using the average keyword we get insignificant digits after the decimal point.
 To reduce the number of insignificant digits after the decimal point, type this query.
 ROUND value 2 limits the result to 2 digits after the decimal point.
 Execute the query.
 The output displays only two digits after the decimal point.
@@ Line 139: / Line 157: @@
 MAX(cgpa)
 || Type the following query.
 The '''MAX''' function returns the largest value in the C G P A column.
@@ Line 145: / Line 164: @@
 '''Cursor on the output.'''
 || Execute the query.
 The result displays the highest C G P A recorded in the students table.
@@ Line 157: / Line 177: @@
 '''MIN(cgpa)'''
 || Type the following query.
 '''MIN''' returns the smallest value in the column.
@@ Line 163: / Line 184: @@
 '''Cursor on the output.'''
 || Execute the query.
 The output displays the lowest C G P A  recorded in the students table.
@@ Line 175: / Line 197: @@
 '''SUM(cgpa)'''
 || Type the following query.
 The '''SUM''' function helps us find the total C G P A  of all students.
@@ Line 181: / Line 204: @@
 '''Cursor on the output.'''
 || Execute the query.
 The output displays the sum of all CGPA values.