Sandhya.np14 at 06:48, 6 February 2019

2019-02-06T06:48:49Z

PoojaMoolya: 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..."

2019-01-30T11:07:23Z

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..."

New page

{| border=1
| '''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, aggregate-function of column2'''

''FROM table-name'''

'''[WHERE condition]'''

'''GROUP BY column1 , 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 see 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, aggregate-function(column2) '''

'''FROM table-name'''

'''[WHERE condition]'''

'''GROUP BY column1 , 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 summarises 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.

|}

RDBMS-PostgreSQL/C2/Aggregation-facilities-in-SQL/English-timed - Revision history

Sandhya.np14 at 06:48, 6 February 2019

PoojaMoolya: 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..."