RDBMS-PostgreSQL/C2/Aggregation-facilities-in-SQL/English-timed
From Script | Spoken-Tutorial
| 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. |
