Difference between revisions of "RDBMS-PostgreSQL/C2/Aggregation-facilities-in-SQL/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "{| border=1 | '''Time''' | '''Narration''' |- |00:01 | Welcome to the''' Spoken Tutorial''' on '''Aggregation facilities in SQL.''' |- |00:07 | In this tutorial we will...")
 
 
Line 9: Line 9:
 
|-  
 
|-  
 
|00:07
 
|00:07
−
| In this tutorial we will learn about  
+
| In this tutorial, we will learn about: '''Group by ''',  '''Having '''and  '''Order by clause'''.
−
 
+
−
'''Group by ''' ,  '''Having '''and  '''Order by clause'''  
+
  
 
|-  
 
|-  
 
|00:16
 
|00:16
−
| To record this tutorial, I am using  '''Ubuntu Linux 14.04''' operating system  
+
| To record this tutorial, I am using: '''Ubuntu Linux 14.04''' operating system,
  
 
|-  
 
|-  
 
|00:23
 
|00:23
−
| '''PostgreSQL 9.3.x'''  
+
| '''PostgreSQL 9.3.x''',
  
 
'''pgAdmin 1.18'''
 
'''pgAdmin 1.18'''
Line 25: Line 23:
 
|-  
 
|-  
 
|00:31
 
|00:31
−
| To follow this tutorial, you should have basic understanding of  
+
| To follow this tutorial, you should have basic understanding of '''SELECT''' statement and  '''Aggregate functions.'''  
−
 
+
−
'''SELECT statement''' and  '''Aggregate functions.'''  
+
  
 
|-  
 
|-  
Line 43: Line 39:
 
|-  
 
|-  
 
|01:00
 
|01:00
−
|'''Aggregate functions''' are mostly used with the '''Group By clause '''of the '''SELECT statement'''.  
+
|'''Aggregate functions''' are mostly used with the '''Group By clause '''of the '''SELECT''' statement.  
  
 
|-  
 
|-  
Line 53: Line 49:
 
| It returns one record for each group.  
 
| It returns one record for each group.  
  
−
'''Group by''' can be done with one or more columns.  
+
'''Group by''' can be done with one or more '''column'''s.  
  
 
|-  
 
|-  
Line 59: Line 55:
 
| The syntax for group by clause is:  
 
| The syntax for group by clause is:  
  
−
'''SELECT column1, aggregate-function of column2'''  
+
'''SELECT column1 comma aggregate-function of column2'''  
  
 
''FROM table-name'''  
 
''FROM table-name'''  
Line 65: Line 61:
 
'''[WHERE condition]'''  
 
'''[WHERE condition]'''  
  
−
'''GROUP BY column1 , column2'''  
+
'''GROUP BY column1 comma column2'''  
  
 
|-  
 
|-  
Line 73: Line 69:
 
|-  
 
|-  
 
| 01:43
 
| 01:43
−
| Type the code as shown here and execute the query.  
+
| Type the code as shown here and '''execute''' the '''query'''.  
  
 
|-  
 
|-  
Line 79: Line 75:
 
| We can see the student details from various departments and cities.  
 
| We can see the student details from various departments and cities.  
 
   
 
   
−
How can we group these records based on a column?  
+
How can we group these '''record'''s based on a '''column'''?  
  
 
|-  
 
|-  
Line 89: Line 85:
 
| Let us write a query.  
 
| Let us write a query.  
  
−
First clear the''' SQL editor window.'''  
+
First, clear the''' SQL editor window.'''  
  
 
|-  
 
|-  
Line 101: Line 97:
 
|-  
 
|-  
 
|02:21
 
|02:21
−
|After grouping, the '''aggregate function Count '''will count the number of records under each department.  
+
|After grouping, the '''aggregate function 'Count' '''will count the number of records under each department.  
  
 
|-  
 
|-  
 
|02:28
 
|02:28
−
|The grouping is based on rows with the same value in the specified column i.e '''deptid'''  
+
|The grouping is based on '''row'''s with the same value in the specified column i.e '''deptid'''  
  
 
|-  
 
|-  
 
|02:36
 
|02:36
−
|Note that the table is not physically rearranged.  
+
|Note that the '''table''' is not physically rearranged.  
  
 
|-  
 
|-  
 
|02:40
 
|02:40
−
| Let’s execute the code and see the output.  
+
| Let’s execute the '''code''' and see the output.  
  
 
|-  
 
|-  
Line 121: Line 117:
 
|-  
 
|-  
 
| 02:49
 
| 02:49
−
| Next, let us see how to add '''where''' condition in the '''Group by clause.'''  
+
| Next, let us see how to add '''where''' condition in the '''Group by''' clause.
  
 
|-  
 
|-  
Line 129: Line 125:
 
|-  
 
|-  
 
|02:57
 
|02:57
−
|Here a '''where condition '''is specified, to display the total number of students in the department ''''CS'.'''  
+
|Here a '''where''' condition is specified to display the total number of students in the department ''''CS'.'''  
  
 
|-  
 
|-  
 
|03:04
 
|03:04
−
|The''' Where clause '''used in a query containing a '''Group By clause''', eliminates the rows before grouping.  
+
|The''' Where''' clause used in a query containing a '''Group By''' clause, eliminates the rows before grouping.  
  
 
|-  
 
|-  
Line 141: Line 137:
 
|-  
 
|-  
 
| 03:14
 
| 03:14
−
| Let us see a few more examples of '''Group by clause.'''  
+
| Let us see a few more examples of '''Group by''' clause.
  
 
|-  
 
|-  
Line 149: Line 145:
 
|-  
 
|-  
 
| 03:21
 
| 03:21
−
| I want to group the '''students table '''to find out the number of male and female students.  
+
| 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'''.  
 
So, we have to group the rows with the column '''gender'''.  
Line 175: Line 171:
 
|-  
 
|-  
 
| 03:52
 
| 03:52
−
| Likewise, we can make use of any '''aggregate function '''in the '''SELECT statement '''with '''Group by clause.'''  
+
| Likewise, we can make use of any '''aggregate function '''in the '''SELECT '''statement with '''Group by''' clause.
  
 
|-  
 
|-  
Line 195: Line 191:
 
|-  
 
|-  
 
|04:21
 
|04:21
−
|The list of column names in the''' SELECT clause '''must appear in the '''Group by clause '''also'''.'''
+
|The list of column names in the''' SELECT''' clause must appear in the '''Group by''' clause also.  
  
−
Let us see execute and see the output.  
+
Let us execute and see the output.  
  
 
|-  
 
|-  
 
| 04:32
 
| 04:32
−
| Next we will learn about the '''Having clause.'''  
+
| Next we will learn about the '''Having '''clause.
  
 
|-  
 
|-  
 
| 04:36
 
| 04:36
−
| '''HAVING clause''' is used to retrieve rows for the specified condition from a grouped result.  
+
| '''HAVING''' clause is used to retrieve rows for the specified condition from a grouped result.  
  
 
|-  
 
|-  
 
|04:43
 
|04:43
−
| Use the '''WHERE clause '''to exclude rows that you don't want to group, whereas  
+
| 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.
−
 
+
−
Use the''' HAVING clause''' to filter rows after they have been grouped.
+
  
 
|-  
 
|-  
Line 217: Line 211:
 
| The syntax is  
 
| The syntax is  
  
−
'''SELECT column1, aggregate-function(column2) '''  
+
'''SELECT column1 comma aggregate-function of column2 '''  
  
 
'''FROM table-name'''  
 
'''FROM table-name'''  
Line 223: Line 217:
 
'''[WHERE condition]'''  
 
'''[WHERE condition]'''  
  
−
'''GROUP BY column1 , column2'''  
+
'''GROUP BY column1 comma column2'''  
  
 
'''HAVING Condition'''  
 
'''HAVING Condition'''  
Line 263: Line 257:
 
|-  
 
|-  
 
| 05:53
 
| 05:53
−
| Here, we are using '''aggregate function count''' in the '''having''' '''clause'''.  
+
| Here, we are using '''aggregate function 'count'''' in the '''having''' clause.  
  
 
That is, the count should be greater than 1.  
 
That is, the count should be greater than 1.  
Line 269: Line 263:
 
|-  
 
|-  
 
|06:02
 
|06:02
−
|Here the student records are grouped by '''department''' and then by '''city'''.  
+
|Here, the student records are grouped by '''department''' and then by '''city'''.  
 
   
 
   
 
The '''having''' condition is applied to the filtered rows.  
 
The '''having''' condition is applied to the filtered rows.  
Line 279: Line 273:
 
|-  
 
|-  
 
| 06:16
 
| 06:16
−
| Next we will see the '''Order by clause.'''  
+
| Next we will see the '''Order by''' clause.
  
 
|-  
 
|-  
 
| 06:19
 
| 06:19
−
|  '''Order By clause''' is used to sort the records in ascending or descending order.  
+
|  '''Order By''' clause is used to sort the records in ascending or descending order.  
  
 
|-  
 
|-  
 
|06:25
 
|06:25
−
| This '''clause''' is always used at the end of the '''SELECT statement.'''  
+
| This '''clause''' is always used at the end of the '''SELECT''' statement.
  
 
|-  
 
|-  
 
|06:30
 
|06:30
−
| The syntax is shown here
+
| The syntax is shown here.
  
 
|-  
 
|-  
Line 303: Line 297:
 
|-  
 
|-  
 
|06:42
 
|06:42
−
|I have added the''' Order by''' '''clause''' to the end of the''' SELECT statement.'''  
+
|I have added the''' Order by''' '''clause''' to the end of the '''SELECT''' statement.
  
 
|-  
 
|-  
Line 331: Line 325:
 
| Type the code.  
 
| Type the code.  
  
−
This query will sort the '''students table '''by '''city''' in ascending order.  
+
This query will sort the '''students''' table by '''city''' in ascending order.  
  
 
|-  
 
|-  
Line 337: Line 331:
 
|Use a comma to separate the columns.  
 
|Use a comma to separate the columns.  
  
−
Then within each '''city, '''it will sort by the student names.  
+
Then, within each '''city, '''it will sort by the student names.  
  
 
|-  
 
|-  
Line 365: Line 359:
 
| Here we can see the sorted result of grouped rows.  
 
| 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.  
+
'''Dept id '''is sorted in ascending order and within each '''department''', the '''city''' is sorted in ascending order.  
  
 
|-  
 
|-  
Line 375: Line 369:
 
|-  
 
|-  
 
| 08:32
 
| 08:32
−
| In this tutorial, we have learnt about''''' the '''clauses'''''' '''
+
| In this tutorial, we have learnt about the clauses:
  
 
'''Group by '''  
 
'''Group by '''  
Line 381: Line 375:
 
'''Having'''  
 
'''Having'''  
  
−
'''Order by'''  
+
'''Order by'''.
  
 
|-  
 
|-  
 
| 08:40
 
| 08:40
−
| As an assignment-  Write a '''Select statement '''to display the number of students with the same '''CGPA'''  
+
| As an assignment-  Write a '''Select''' statement to display the number of students with the same '''CGPA'''.
  
 
|-  
 
|-  
 
| 08:47
 
| 08:47
−
|  Hint: Group the rows by '''CGPA''' column  
+
|  Hint: Group the rows by '''CGPA''' column.
  
 
|-  
 
|-  
 
| 08:51
 
| 08:51
−
| The video at the following link summarises the Spoken Tutorial project.  
+
| The video at the following link summarizes the '''Spoken Tutorial''' project.  
  
 
Please download and watch it.  
 
Please download and watch it.  
Line 399: Line 393:
 
|-  
 
|-  
 
| 08:58
 
| 08:58
−
| The''' Spoken Tutorial Project''' Team
+
| The''' Spoken Tutorial Project''' team conducts workshops and  gives certificates.
−
 
+
−
conducts workshops and  gives certificates  
+
  
 
For more details, please write to us.  
 
For more details, please write to us.  
Line 411: Line 403:
 
|-  
 
|-  
 
| 09:11
 
| 09:11
−
| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.  
+
| Spoken Tutorial project is funded by '''NMEICT, MHRD''', Government of India.  
  
 
More information on this mission is available at this link.  
 
More information on this mission is available at this link.  
Line 417: Line 409:
 
|-  
 
|-  
 
| 09:22
 
| 09:22
−
| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.  
+
| This is Nirmala Venkat from '''IIT Bombay''', signing off. Thanks for watching.  
  
 
|}
 
|}

Latest revision as of 12:18, 6 February 2019

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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=RDBMS-PostgreSQL/C2/Aggregation-facilities-in-SQL/English-timed&oldid=45707"