RDBMS-PostgreSQL/C2/Aggregation-facilities-in-SQL/English

From Script | Spoken-Tutorial
Jump to: navigation, search
Visual Cue Narration
Slide 1:


Aggregation facilities in SQL

Welcome to the Spoken Tutorial on Aggregation facilities in SQL.
Slide 2:

Learning Objectives

  • Group by
  • Having
  • Order by clause
In this tutorial we will learn about
  • Group by
  • Having and
  • Order by clause
Slide 3:

System requirement

To record this tutorial, I am using
  • Ubuntu Linux 14.04 operating system
  • PostgreSQL 9.3.x
  • pgAdmin 1.18
Modify this slideSlide 4:

Pre-requisites

To follow this tutorial, you should have basic understanding of
  • SELECT statement and
  • Aggregate functions.
  • For more details, refer to the previous RDBMS PostgreSQL tutorials on this website.
Slide 5:

Aggregate Functions

  • Aggregate functions perform calculation on a set of values and return a single value.
  • Aggregate functions are mostly used with the Group By clause of the SELECT statement.
Aggregate functions perform calculations on a set of values and return a single value.

We learnt about these functions in the earlier tutorials.

Aggregate functions are mostly used with the Group By clause of the SELECT statement.

Slide 5(a):

GROUP BY clause

  • Group by clause is used to collect identical data in groups.
  • It returns one record for each group.
  • Group by can be done with one or more columns.
Slide 5(b):

GROUP BY

Syntax:

SELECT column1, aggregate-function(column2) FROM table-name [WHERE condition]

GROUP BY column1, column2...

The syntax for group by clause is:

SELECT column1, aggregate-function of column2

FROM table-name

[WHERE condition]

GROUP BY column1 , column2

Switch to pgAdmin Let’s open the SQL Editor window in pgAdmin for demonstration.
Type the code

Select studentname, city, cgpa, deptid from students

Type the code as shown here and execute the query.
Point to the output We can see the student details from various departments and cities.

How can we group these records based on a column?

Say, we want to display how many students are there in each department.
Clear the SQL editor window. Let us write a query.

First clear the SQL editor window.

Type the code Now type the code as shown here.
Select deptid, count(*) from students group by deptid This query will group the records in the students table with the deptid column.

After grouping, the aggregate function Count will count the number of records under each department.

The grouping is based on rows with the same value in the specified column i.e deptid

Note that the table is not physically rearranged.

Point to the output Let’s execute the code and see the output.

The number of students in each department is displayed here as output.

Next, let us see how to add where condition in the Group by clause.
Type the code

Select deptid, count(*) from students where deptid = 'CS' group by deptid

We will change the same code to add a condition.

Here a where condition is specified, to display the total number of students in the department 'CS'.

The Where clause used in a query containing a Group By clause, eliminates the rows before grouping.

Execute the code now to see the output.
<<PAUSE>>

Let us see a few more examples of Group by clause.

Type the code Type the code as shown here.
Select gender, count(*) from students group by gender I want to group the students table to find out the number of male and female students.

So, we have to group the rows with the column gender.

Execute Execute the query.
Point to the output We can see the output as expected.

<<PAUSE>>

Now I want to find out the maximum CGPA score of students from each city.
Select city, count(*), max(CGPA) from students group by city For this, I'll type the query as shown.
Execute Now execute the code to see the output.

<<PAUSE>>

Likewise, we can make use of any aggregate function in the SELECT statement with Group by clause.
Next we will see how to use more than one column in Group by clause.
Type the code Type the code as shown here.
Select deptid, city, count(*) from students group by deptid, city

Highlight the column list

Here, after group by you can see two columns deptid and city.

First it will group the records with department and then group with city, for each department.

The list of column names in the SELECT clause must appear in the Group by clause also.

Execute the query. Let us see execute and see the output.
Next we will learn about the Having clause.
Slide 6(a):

HAVING clause

  • HAVING clause is used to retrieve rows for the specified condition from a grouped result.
  • Use the WHERE clause to exclude rows that you don't want to group, whereas
  • Use the HAVING clause to filter rows after they have been grouped.
Slide 6(b):

Having

Syntax:

SELECT column1, aggregate-function(column2) FROM table-name [WHERE condition]

GROUP BY column1, column2...

HAVING Condition

The syntax is as s

SELECT column1, aggregate-function(column2)

FROM table-name

[WHERE condition]

GROUP BY column1 , column2

HAVING Condition

Let us see an example for this.

Switch to the SQL Editor window.

Type the code

Select deptid, city, count(*) from students group by deptid, city

having city = 'Chennai'

We will add the condition 'having city= 'Chennai'.

The HAVING clause comes after the Group By clause.

Note that you can apply HAVING clause only to

  • columns that appear in the Group By clause
  • or an aggregate function.
Execute to see the result. Execute and see the result.
Point to the output. We can see two rows as output.

There are 3 students from CS department and 1 student from EE department.

The condition specifies that the students are from Chennai.

Let’s see another example.
Select deptid, city, count(*) from students group by deptid, city

having count(*) > 1

Here, we are using aggregate function count in the having clause.

That is, the count should be greater than 1.

Here the student records are grouped by department and then by city.

The having condition is applied to the filtered rows.

Click on execute query button. Once again, execute to see the result.
Next we will see the Order by clause.
Slide 7(a):

Order by

* Order By clause is used to sort the records in ascending or descending order.
  • This clause is always used at the end of the SELECT statement.
Slide 7(b):

Syntax:

Order by

SELECT column-list

FROM table_name [WHERE condition]

[ORDER BY column1, column2, .. ] [ASC | DESC];

The syntax is shown here:

SELECT column-list FROM table-name

[WHERE condition]

ORDER BY [column1, column2... ]

[ASC|DESC]

Let us switch back to SQL Editor window for demonstration.
Type the code

Select deptid, count(*) from students group by deptid order by count(*)

Type the code as shown on the screen.

I have added the Order by clause to the end of the SELECT statement.

Now execute the query.

We see that the total number of students in each department is sorted in ascending order.

By default, the sorting is always done in ascending order.

Type the code

Select deptid, count(*) from students group by deptid order by count(*) desc

Type DESC at the end of the query to sort in descending order.
Click on execute query button. Execute the query to see the output.
We can also sort result set on multiple columns.
Type the code,

Select studentname, city from students order by city, studentname

Highlight the comma

Type the code.

This query will sort the students table by city in ascending order.

Use a comma to separate the columns.

Then within each city, it will sort by the student names.

Columns specified in ORDER BY clause must be one of the columns selected in the SELECT column list.

Select studentname, city from students order by city Desc, studentname Asc Let us modify the same query to see city in descending order and the student name in ascending order.
Execute to see the output.
We will see another example of order by in Group by clause.

Execute the query to see the output.

Select deptid, city, count(*) from students

group by deptid, city order by deptid, city

Here we can see the sorted result of grouped rows.

Dept id is sorted in ascending order and within each department, the city is sorted in ascending order.

With this we come to the end of this tutorial.

Let us summarize.

Summary In this tutorial, we have learnt about the clauses'
  • Group by
  • Having
  • Order by
Assignment As an assignment-
  • Write a Select statement to display the number of students with the same CGPA
  • Hint: Group the rows by CGPA column
Slide 12:

About Spoken Tutorial project

The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

Slide 13:

Spoken Tutorial workshops

The Spoken Tutorial Project Team
  • conducts workshops
  • gives certificates

For more details, please write to us.

Slide 13:

Forum for specific questions

Please post your timed queries in this forum.
Slide 14:

Acknowledgement

Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at

this link.

This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nirmala Venkat