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