PostgreSQL-Database/C2/Aggregate-Functions/English

From Script | Spoken-Tutorial
Jump to: navigation, search

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, AVG, SUM, MIN, and MAX.


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=57678"