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

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