Nancyvarkey at 11:34, 23 June 2017

2017-06-23T11:34:00Z

Nirmala Venkat: Created page with "'''>>Title of script''':''' Select with Aggregate functions''' '''Author: Nirmala Venkat''' '''Keywords: RDBMS, PostgreSQL, pgAdmin, DISTINCT, LIKE, IN, IS NULL, BETWEEN, ag..."

2017-06-15T11:52:02Z

Created page with "'''>>Title of script''':''' Select with Aggregate functions''' '''Author: Nirmala Venkat''' '''Keywords: RDBMS, PostgreSQL, pgAdmin, DISTINCT, LIKE, IN, IS NULL, BETWEEN, ag..."

New page

'''>>Title of script''':''' Select with Aggregate functions'''

'''Author: Nirmala Venkat'''

'''Keywords: RDBMS, PostgreSQL, pgAdmin, DISTINCT, LIKE, IN, IS NULL, BETWEEN, aggregate functions, video tutorial'''

{| style="border-spacing:0;"
| style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Visual Cue'''
| style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Narration'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 1:'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Welcome to the''' Spoken Tutorial''' on''' Select with Aggregate functions.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 2:'''

'''Learning Objectives'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In this tutorial we will learn

More clauses that can be used with the '''select statement, '''such as-

* '''Distinct'''
* '''Between'''
* '''Like'''
* '''In'''
* '''Is Null''' and

'''Aggregate functions'''
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 3:'''

'''System requirement'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| To record this tutorial, I am using

* '''Ubuntu Linux 14.04''' operating system
* '''PostgreSQL 9.3.x'''
* '''pgAdmin 1.18'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 4:'''

'''Pre-requisites'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| To follow this tutorial, you should have basic understanding of database and tables.

For more details, refer to the '''RDBMS – PostgreSQL''' series on this website.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let’s begin.

First we will learn about the '''DISTINCT''' clause.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 5:'''

'''DISTINCT'''

Note: Duplicate values are not removed from the database
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''DISTINCT keyword '''is used in the '''SELECT statement '''to list distinct and unique values.

It removes the duplicate rows or column values from the query result.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 5:'''

'''DISTINCT'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| The syntax is as follows:

'''SELECT DISTINCT column-names FROM table-name WHERE [condition]'''
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Open pgAdmin
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us open the '''pgAdmin '''for the demonstration.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the SQL icon on the toolbar
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the '''SQL''' icon to open the '''SQL Editor.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type

'''select distinct city from students'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown here.

This '''select statement '''will retrieve the unique city names from the '''students table'''.

It will eliminate duplicate values from the specified column i.e. '''city'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Execute the query
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Execute the query to see the output.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next, we will learn about the '''BETWEEN operator.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 6:'''

'''BETWEEN'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''BETWEEN operator''' is used to retrieve values within a given range.

The syntax is:

'''SELECT column-names FROM table-name WHERE column-name BETWEEN value1 and value2;'''

|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let's see an example for between operator.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let's clear the screen and type the code for the query.

Say, I want to retrieve details of students who are born between January 1995 and January 1996.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type and execute the query'''

'''Select * from students where dob between '1995-01-01' and '1996-01-01''''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let's see the output.

Notice the output shows the list of students as expected.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| We will see another example for '''BETWEEN operator.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type and execute the code'''

'''Select * from students where cgpa between 7.8 and 8.5'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This '''query''' will show all rows from the''' students table,''' where the '''cgpa''' value is between 7.8 and 8.5.

Notice from the output that the value is inclusive of 7.8 and 8.5.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type the code and show the result'''

'''Select * from students where cgpa >= 7.8 and cgpa <= 8.5'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| The same '''query '''can also be written as shown here.

Instead of '''BETWEEN operator''', we can use-

* '''greater than or equal to '''and
* '''lesser than or equal to operators'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Execute the query
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Execute the query to see the output.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will learn about the '''LIKE operator.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 7(a):'''

'''LIKE'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''LIKE operator''' is used to match text values against a given pattern.

It allows '''wildcard characters '''to be used within the '''WHERE clause'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 7(b):'''

'''LIKE'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| There are two '''wildcards '''that can be used in conjunction with the '''LIKE operator''':

'''Percent (%):'''

The '''percent '''sign matches any sequence of string of any length.

'''Underscore (_):'''

The '''underscore '''matches a single character.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| We will see few examples using '''percent wildcard.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Suppose I don't remember the exact name of a student.

But I remember that the student’s name begins with the letter ''''Ra''''.

It is very difficult to search the entire '''students table, '''as there are many rows in the '''table.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type the code'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| To make the search easy, we can use the '''LIKE operator''' as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where studentname like 'Ra %';'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Here, all the student's names start with ''''Ra'''' and is followed by any sequence of characters.

This is called '''pattern matching'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us see a few more examples.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where studentname like '%la';'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown.

This '''query '''returns those rows where the students’ names end with ''''la'.'''

There can be any number of characters before ''''la'.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where studentname like '%am%';'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us see another pattern.

This '''statement''' returns all the rows where the student’s name has''' 'am' '''in the middle.

Note''' percent '''sign before and after ''''am'''' .

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will see how to use '''underscore '''to match a single character.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where studentname like '_a_';'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown.

This will fetch rows of students whose name has three characters and has ''''a'''' in the 2<sup>nd</sup> position.

|-
| style="border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where studentname like '_r%’;'''

| style="border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Underscore 'r' percent '''matches students whose names begin with a single character.

It is followed by ''''r'''' and ends with any number of characters.

Let's see the output.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will see how to use '''IN operator.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 8:'''

'''IN'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Use '''IN operator '''in the '''WHERE clause '''to check if the value matches any value in a given list.

The syntax is as follows:

'''Select column-name from table-name WHERE column-name IN ( value1, value2...)'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us see an example.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where city IN ('Chennai', 'Nagpur', 'Hyderabad')'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This '''statement''' will

* return all rows from the '''students table '''

* where the city is either '''Chennai, Nagpur '''or '''Hyderabad'''.

It helps to reduce the usage of multiple '''OR conditions''' to get the same result.

Let us see the output

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next we will see how to use the '''IS NULL operator '''in the '''select statement'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type the code'''

'''Select * from students where dob is null;'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This '''statement '''will list down the records where the data of birth column is empty.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Type the code'''

'''Select * from students where dob is not null;'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In the same way, we can also use the '''IS NOT NULL operator''', as shown here.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us now learn about '''aggregate functions'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 5:'''

'''Aggregate function'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Aggregate functions '''are '''PostgreSQL built-in functions.'''

It operates on several rows of a query and returns a single result.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Slide 6:'''

'''List of aggregate functions'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''List of aggregate functions'''

'''Count''' – Returns the number of rows

'''Sum''' – Returns the sum of a selected column

'''Max''' - Returns the largest value of a specified column

'''Min''' – Returns the smallest value of a specified column

'''Avg''' – Returns the average value for a specified column

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Switch back to '''SQL Editor''' window.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type,

SELECT count(*) FROM students;

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let’s say, we want to count the number of existing records in the '''students table.'''

For that, type the code as shown here.

The '''count open parentheses asterisk close parentheses function '''returns the number of rows that are fetched by the '''SELECT statement.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the execute button.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Execute this query to see the output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| There are 14 records in the '''students table.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| SELECT count(dob) from students;

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| We can also specify the column name in the '''function '''as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on the execute button.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Once again execute the query.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the output
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Why is it showing 13?

Because the '''count function '''only counts values which are not '''NULL'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us see the output when we specify the '''dob''' column.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Select student name, dob from students
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code and execute.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Point to the output
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Here we can see the '''dob''' column value is '''NULL''' for the student ''''Ram charan'.'''

I have left '''dob''' column blank during record creation for this student.

So the''' count function''' displays the output as 13.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Next let us see how to use the''' SUM function.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Click on clear edit window icon.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let’s clear the screen.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type,

select sum(cgpa) from students
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown here and execute the query.

This returns the summed-up value of the '''cgpa''' column.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type,

select sum(cgpa) from students where city='Mumbai'

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| You can also give an additional ''''Where' condition''' as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Highlight/Point to output
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This returns the sum of '''cgpa''' values of the students who are from '''Mumbai''' city.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us find the maximum '''cgpa''' score.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type,

select Max(cgpa) from students
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This '''select statement '''returns the maximum '''cgpa''' score from the '''students table'''.

9.6 is the highest '''cgpa''' score in the '''students''' table.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Likewise, you can try the remaining '''aggregate functions '''on your own.
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| With this we come to the end of this tutorial.

Let us summarize.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Summary
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In this tutorial, we have learnt about

* More clauses that can be used with the '''select statement, '''such as-
** '''Distinct'''
** '''Between'''
** '''Like'''
** '''In'''
** '''Is Null''' and
* '''Aggregate functions'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Assignment

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| As an assignment, write '''select statements''' to

# Display all columns from '''students table''' where the student name ends with ''''a''''
# Display the minimum and average of the '''cgpa''' column.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 12:

About Spoken Tutorial project

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 13:

Spoken Tutorial workshops

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| The''' Spoken Tutorial Project''' Team

* conducts workshops
* gives certificates

For more details, please write to us.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 13:

Forum for specific questions
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Please post your timed queries in this forum.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Slide 14:

Acknowledgement

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at

this link.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

|}

RDBMS-PostgreSQL/C2/Select-with-Aggregate-functions/English - Revision history

Nancyvarkey at 11:34, 23 June 2017

Nancyvarkey at 12:37, 18 June 2017

Nirmala Venkat at 11:52, 15 June 2017

Nirmala Venkat: Created page with "'''>>Title of script''':''' Select with Aggregate functions''' '''Author: Nirmala Venkat''' '''Keywords: RDBMS, PostgreSQL, pgAdmin, DISTINCT, LIKE, IN, IS NULL, BETWEEN, ag..."

@@ Line 236: / Line 236: @@
 |-
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| '''Select * from students where studentname like '_a_';'''
-| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Type the code as shown.
+| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| This will fetch rows of students whose name has three characters and has ''''a'''' in the 2<sup>nd</sup> position.
-−
 This will fetch rows of students whose name has three characters and has ''''a'''' in the 2<sup>nd</sup> position.
 |-
@@ Line 261: / Line 259: @@
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Use '''IN operator '''in the '''WHERE clause '''to check if the value matches any value in a given list.
-The syntax is as follows:
+The syntax is:
 '''Select column-name from table-name WHERE column-name IN ( value1, value2...)'''
@@ Line 303: / Line 301: @@
 |-
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"|
-| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us now learn about '''aggregate functions'''.
+| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| Let us now learn about '''aggregate function'''.
 |-

@@ Line 18: / Line 18: @@
 '''Learning Objectives'''
-| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In this tutorial we will learn
+| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.062cm;padding-right:0.191cm;"| In this tutorial we will learn more '''clauses''' that can be used with the '''select statement, '''such as-
-−
-More clauses that can be used with the '''select statement, '''such as-
 * '''Distinct'''
@@ Line 27: / Line 25: @@
 * '''In'''
 * '''Is Null''' and
 * '''Aggregate functions'''
 |-