Difference between revisions of "PostgreSQL-Database/C2/Aggregate-Functions/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with " '''Title of the script: Aggregate Functions''' '''Authors:''' EduPyramids.''' '''Keywords:''' PostgreSQL, pgAdmin 4, Aggregate Functions, COUNT, AVG(average), SUM, MIN, MA...")
 
(One intermediate revision by the same user not shown)
Line 13: Line 13:
  
 
|-
 
|-
−
|| '''Title Slide'''
+
|| '''Slide 1'''
 +
 
 +
'''Title Slide'''
 
|| Welcome to this Spoken Tutorial on '''Aggregate Functions'''.
 
|| Welcome to this Spoken Tutorial on '''Aggregate Functions'''.
  
 
|-
 
|-
 
|| '''Slide 2'''
 
|| '''Slide 2'''
 +
 
'''Learning Objectives'''
 
'''Learning Objectives'''
−
|| In this tutorial, we will learn about
+
|| In this tutorial, we will learn about:
 
* '''Aggregate Functions'''
 
* '''Aggregate Functions'''
  
 
|-
 
|-
 
|| '''Slide 3'''
 
|| '''Slide 3'''
 +
 
'''System Requirements'''
 
'''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
 +
* '''PgAdmin 4 version 9.11'''.
 
|| To record this tutorial, I am using the following setup.
 
|| To record this tutorial, I am using the following setup.
  
 
|-
 
|-
 
|| '''Slide 4'''
 
|| '''Slide 4'''
 +
 
'''Pre-requisites'''
 
'''Pre-requisites'''
 +
 
|| To follow this tutorial,
 
|| 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.
  
 
|-
 
|-
 
|| '''Slide 5'''
 
|| '''Slide 5'''
 +
 
'''Code Files'''
 
'''Code Files'''
 +
 
The following code file is required to practice this tutorial:
 
The following code file is required to practice this tutorial:
 +
 
'''af-commands.txt'''
 
'''af-commands.txt'''
 +
 
This file is provided in the Code Files link on this tutorial page.
 
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.
 
|| The following code file is required to practice this tutorial.
 +
 
This file is provided in the Code Files link of this tutorial page.
 
This file is provided in the Code Files link of this tutorial page.
 +
 
Please download and extract the file.
 
Please download and extract the file.
  
Line 53: Line 69:
 
|-
 
|-
 
|| '''Slide 6'''
 
|| '''Slide 6'''
 +
 
'''Aggregate Functions'''
 
'''Aggregate Functions'''
 
|| '''Aggregate''' functions perform calculations on multiple rows and return a single value.
 
|| '''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 60: Line 78:
 
||  
 
||  
 
|| I have opened the '''p g Admin 4''' interface and connected to the localhost server.
 
|| I have opened the '''p g Admin 4''' interface and connected to the localhost server.
 +
 
Let us learn these functions using query examples.
 
Let us learn these functions using query examples.
 +
 
This is the student table.
 
This is the student table.
 +
 
First, we will learn about the '''COUNT''' function.
 
First, we will learn about the '''COUNT''' function.
  
Line 67: Line 88:
 
|| '''Type:'''
 
|| '''Type:'''
 
SELECT COUNT(*) FROM students;'''
 
SELECT COUNT(*) FROM students;'''
 +
 
'''Highlight COUNT(*)
 
'''Highlight COUNT(*)
 +
 
Click on the Execute Query button.
 
Click on the Execute Query button.
 
|| Let us type the following query.
 
|| Let us type the following query.
 +
 
The '''COUNT''' asterisk function returns the total number of rows in the table.
 
The '''COUNT''' asterisk function returns the total number of rows in the table.
 +
 
It counts all records including those containing '''NULL''' values.
 
It counts all records including those containing '''NULL''' values.
 +
 
Let us execute the query.
 
Let us execute the query.
 +
 
This shows the total number of students stored in the table.
 
This shows the total number of students stored in the table.
  
Line 82: Line 109:
 
|| '''Type:'''
 
|| '''Type:'''
 
'''SELECT COUNT(cgpa) FROM students;'''
 
'''SELECT COUNT(cgpa) FROM students;'''
 +
 
'''Highlight:'''
 
'''Highlight:'''
 
'''COUNT(cgpa)'''
 
'''COUNT(cgpa)'''
 +
 
Click on the Execute Query button.
 
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.
 
The '''COUNT''' function counts the number of not '''NULL''' values in the '''C G P A''' column.
 +
 
Let us execute the query.
 
Let us execute the query.
  
Line 96: Line 127:
 
|| Show both the outputs in different query windows.
 
|| Show both the outputs in different query windows.
 
|| Here, '''COUNT asterisk''' returns all the rows.
 
|| Here, '''COUNT asterisk''' returns all the rows.
 +
 
However, the COUNT '''column''' counts only '''not NULL''' values of the specified column.
 
However, the COUNT '''column''' counts only '''not NULL''' values of the specified column.
  
Line 114: Line 146:
 
|-
 
|-
 
|| Click on the Execute Query button.
 
|| Click on the Execute Query button.
 +
 
'''Cursor on the output.'''
 
'''Cursor on the output.'''
 
|| Let us execute the query.
 
|| Let us execute the query.
 +
 
The output displays the average c g p a of all students.
 
The output displays the average c g p a of all students.
  
 
|-
 
|-
 
|| Type this command:
 
|| Type this command:
 +
 
'''SELECT ROUND(AVG(cgpa), 2)'''
 
'''SELECT ROUND(AVG(cgpa), 2)'''
 
'''FROM students;'''
 
'''FROM students;'''
 +
 
Click on the execute button.
 
Click on the execute button.
 
|| Observe that, using the average keyword we get insignificant digits after the decimal point.
 
|| 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.
 
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.
 
ROUND value 2 limits the result to 2 digits after the decimal point.
 +
 
Execute the query.
 
Execute the query.
 +
 
The output displays only two digits after the decimal point.
 
The output displays only two digits after the decimal point.
  
Line 136: Line 176:
 
|| '''Type:'''
 
|| '''Type:'''
 
'''SELECT MAX(cgpa) FROM students;'''
 
'''SELECT MAX(cgpa) FROM students;'''
 +
 
'''Highlight:'''
 
'''Highlight:'''
 
MAX(cgpa)
 
MAX(cgpa)
 
|| Type the following query.
 
|| Type the following query.
 +
 
The '''MAX''' function returns the largest value in the C G P A column.
 
The '''MAX''' function returns the largest value in the C G P A column.
  
 
|-
 
|-
 
|| Click on the Execute Query button.
 
|| Click on the Execute Query button.
 +
 
'''Cursor on the output.'''
 
'''Cursor on the output.'''
 
|| Execute the query.
 
|| Execute the query.
 +
 
The result displays the highest C G P A recorded in the students table.
 
The result displays the highest C G P A recorded in the students table.
  
Line 154: Line 198:
 
|| '''Type:'''
 
|| '''Type:'''
 
'''SELECT MIN(cgpa) FROM students;'''
 
'''SELECT MIN(cgpa) FROM students;'''
 +
 
'''Highlight:'''
 
'''Highlight:'''
 
'''MIN(cgpa)'''
 
'''MIN(cgpa)'''
 
|| Type the following query.
 
|| Type the following query.
 +
 
'''MIN''' returns the smallest value in the column.
 
'''MIN''' returns the smallest value in the column.
  
 
|-
 
|-
 
|| Click on the Execute Query button.
 
|| Click on the Execute Query button.
 +
 
'''Cursor on the output.'''
 
'''Cursor on the output.'''
 
|| Execute the query.
 
|| Execute the query.
 +
 
The output displays the lowest C G P A  recorded in the students table.
 
The output displays the lowest C G P A  recorded in the students table.
  
Line 172: Line 220:
 
|| '''Type:'''
 
|| '''Type:'''
 
'''SELECT SUM(cgpa) FROM students;'''
 
'''SELECT SUM(cgpa) FROM students;'''
 +
 
'''Highlight:'''
 
'''Highlight:'''
 
'''SUM(cgpa)'''
 
'''SUM(cgpa)'''
 
|| Type the following query.
 
|| Type the following query.
 +
 
The '''SUM''' function helps us find the total C G P A  of all students.
 
The '''SUM''' function helps us find the total C G P A  of all students.
  
 
|-
 
|-
 
|| Click on the Execute Query button.
 
|| Click on the Execute Query button.
 +
 
'''Cursor on the output.'''
 
'''Cursor on the output.'''
 
|| Execute the query.
 
|| Execute the query.
 +
 
The output displays the sum of all CGPA values.
 
The output displays the sum of all CGPA values.
  
Line 189: Line 241:
 
|-
 
|-
 
|| '''Slide 10'''
 
|| '''Slide 10'''
 +
 
'''Summary'''
 
'''Summary'''
 +
 
In this tutorial, we have learnt about
 
In this tutorial, we have learnt about
−
• '''Aggregate Functions'''
+
* '''Aggregate Functions'''
 
|| Let us summarise.
 
|| Let us summarise.
  
 
|-
 
|-
 
|| '''Slide 11'''
 
|| '''Slide 11'''
 +
 
'''Assignment'''
 
'''Assignment'''
 +
 
'''As an 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.
 +
* Calculate the total CGPA of students from each city.
 
|| We encourage you to do this assignment.
 
|| We encourage you to do this assignment.
  
 
|-
 
|-
 
|| '''Slide 12'''
 
|| '''Slide 12'''
−
This '''Spoken Tutorial''' is brought to you by '''EduPyramids''' Educational Private Limited SINE IIT Bombay.
+
 
 +
This '''Spoken Tutorial''' is brought to you by '''EduPyramids''' Educational Private Limited. SINE IIT Bombay.
 +
 
 
'''Thank you'''
 
'''Thank you'''
 
|| Thank you.
 
|| Thank you.
  
 
|}
 
|}

Revision as of 12:59, 23 April 2026

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.


Visual Cue Narration
Slide 1

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 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:

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

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.

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.

Contributors and Content Editors

ANJALISATDIVE, Madhurig

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/Aggregate-Functions/English&oldid=57677"