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.

Contributors and Content Editors

PoojaMoolya, Sandhya.np14