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

From Script | Spoken-Tutorial
Jump to: navigation, search
Time Narration
00:01 Welcome to the Spoken Tutorial on Aggregation facilities in SQL.
00:07 In this tutorial, we will learn about: Group by , Having and Order by clause.
00:16 To record this tutorial, I am using: Ubuntu Linux 14.04 operating system,
00:23 PostgreSQL 9.3.x,

pgAdmin 1.18

00:31 To follow this tutorial, you should have basic understanding of SELECT statement and Aggregate functions.
00:41 For more details, refer to the previous RDBMS PostgreSQL tutorials on this website.
00:48 Aggregate functions perform calculations on a set of values and return a single value.
00:55 We learnt about these functions in the earlier tutorials.
01:00 Aggregate functions are mostly used with the Group By clause of the SELECT statement.
01:06 Group by clause is used to collect identical data in groups.
01:12 It returns one record for each group.

Group by can be done with one or more columns.

01:21 The syntax for group by clause is:

SELECT column1 comma aggregate-function of column2

FROM table-name'

[WHERE condition]

GROUP BY column1 comma column2

01:37 Let’s open the SQL Editor window in pgAdmin for demonstration.
01:43 Type the code as shown here and execute the query.
01:50 We can see the student details from various departments and cities.

How can we group these records based on a column?

02:00 Say, we want to display how many students are there in each department.
02:06 Let us write a query.

First, clear the SQL editor window.

02:11 Now type the code as shown here.
02:15 This query will group the records in the students table with the deptid column.
02:21 After grouping, the aggregate function 'Count' will count the number of records under each department.
02:28 The grouping is based on rows with the same value in the specified column i.e deptid
02:36 Note that the table is not physically rearranged.
02:40 Let’s execute the code and see the output.
02:44 The number of students in each department is displayed as output.
02:49 Next, let us see how to add where condition in the Group by clause.
02:54 We will change the same code to add a condition.
02:57 Here a where condition is specified to display the total number of students in the department 'CS'.
03:04 The Where clause used in a query containing a Group By clause, eliminates the rows before grouping.
03:11 Execute the code now to see the output.
03:14 Let us see a few more examples of Group by clause.
03:18 Type the code as shown here.
03:21 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.

03:31 Execute the query.
03:33 We can see the output as expected.
03:36 Now I want to find out the maximum CGPA score of students from each city.
03:43 For this, I'll type the query as shown.
03:48 Now execute the code to see the output.
03:52 Likewise, we can make use of any aggregate function in the SELECT statement with Group by clause.
03:59 Next we will see how to use more than one column in Group by clause.
04:05 Type the code as shown here.
04:08 Here, after group by you can see two columns deptid and city.
04:14 First it will group the records with department and then group with city, for each department.
04:21 The list of column names in the SELECT clause must appear in the Group by clause also.

Let us execute and see the output.

04:32 Next we will learn about the Having clause.
04:36 HAVING clause is used to retrieve rows for the specified condition from a grouped result.
04:43 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.
04:54 The syntax is

SELECT column1 comma aggregate-function of column2

FROM table-name

[WHERE condition]

GROUP BY column1 comma column2

HAVING Condition

05:09 Let us see an example for this.

Switch to the SQL Editor window.

05:15 We will add the condition 'having city= 'Chennai'.

The HAVING clause comes after the Group By clause.

05:24 Note that you can apply HAVING clause only to columns that appear in the Group By clause or an aggregate function.
05:32 Execute and see the result.
05:35 We can see two rows as output.

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

05:45 The condition specifies that the students are from Chennai.
05:50 Let’s see another example.
05:53 Here, we are using aggregate function 'count' in the having clause.

That is, the count should be greater than 1.

06:02 Here, the student records are grouped by department and then by city.

The having condition is applied to the filtered rows.

06:11 Once again, execute to see the result.
06:16 Next we will see the Order by clause.
06:19 Order By clause is used to sort the records in ascending or descending order.
06:25 This clause is always used at the end of the SELECT statement.
06:30 The syntax is shown here.
06:34 Let us switch back to SQL Editor window for demonstration.
06:38 Type the code as shown on the screen.
06:42 I have added the Order by clause to the end of the SELECT statement.
06:47 Now execute the query.

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

06:56 By default, the sorting is always done in ascending order.
07:01 Type DESC at the end of the query to sort in descending order.
07:07 Execute the query to see the output.
07:11 We can also sort result set on multiple columns.
07:16 Type the code.

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

07:23 Use a comma to separate the columns.

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

07:31 Columns specified in the ORDER BY clause must be one of the columns selected in the SELECT column list.
07:38 Let us see the output. Here the city is sorted in ascending order and within each city the student name is sorted in ascending order.
07:50 Let us modify the same query to see city in descending order and the student name in ascending order.
07:58 Execute to see the output.
08:03 We will see another example of order by in Group by clause.

Execute the query to see the output.

08:11 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.

08:25 With this we come to the end of this tutorial.

Let us summarize.

08:32 In this tutorial, we have learnt about the clauses:

Group by

Having

Order by.

08:40 As an assignment- Write a Select statement to display the number of students with the same CGPA.
08:47 Hint: Group the rows by CGPA column.
08:51 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

08:58 The Spoken Tutorial Project team conducts workshops and gives certificates.

For more details, please write to us.

09:07 Please post your timed queries in this forum.
09:11 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

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

Contributors and Content Editors

PoojaMoolya, Sandhya.np14