Nirmala Venkat at 11:01, 1 August 2018

2018-08-01T11:01:51Z

Nirmala Venkat: Created page with " {| style="border-spacing:0;" | style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Vi..."

2017-10-05T07:29:14Z

Created page with " {| style="border-spacing:0;" | style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Vi..."

New page

{| style="border-spacing:0;"
| style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Visual Cue'''
| style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Narration'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 1:'''

'''Aggregation facilities in SQL'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Welcome to the''' Spoken Tutorial''' on '''Aggregation facilities in SQL.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 2:'''

'''Learning Objectives'''

* '''Group by '''
* '''Having '''
* '''Order by clause'''

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| In this tutorial we will learn about

* '''Group by '''
* '''Having '''and
* '''Order by clause'''
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 3:'''

'''System requirement'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| To record this tutorial, I am using

* '''Ubuntu Linux 14.04''' operating system
* '''PostgreSQL 9.3.x'''
* '''pgAdmin 1.18'''<br/>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Modify this slide'''Slide 4:'''

'''Pre-requisites'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| done'''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.

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''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'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 5(a):'''

'''GROUP BY clause'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
* '''Group by clause '''is used to collect identical data in groups.
* It returns one change in the sliderecorddone for each group.
* '''Group by''' can be done with one or more columns.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 5(b):'''

'''GROUP BY '''

'''Syntax:'''

SELECT column1, aggregate-function(column2) FROM table-name [WHERE condition]

GROUP BY column1, column2...
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| The syntax for group by clause is:

'''SELECT column1, aggregate-function of column2'''

'''FROM table-name'''

'''[WHERE condition]'''

'''GROUP BY column1 , column2'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Switch to pgAdmin
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let’s open the '''SQL Editor window '''in''' pgAdmin '''for demonstration.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code

Select studentname, city, cgpa, deptid from students
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code as shown here and execute the query.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Point to the output
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| We can see the student details from various departments and cities.

How can we group these records based on a column?

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Say, we want to display how many students are there in each department.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Clear the SQL editor window.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let us write a query.

First clear the''' SQL editor window.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Now type the code as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select deptid, count(*) from students group by deptid

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Point to the output
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let’s execute the code and see the output.

The number of students in each department is displayed here as output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Next, let us see how to add '''where''' condition in the '''Group by clause.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code

Select deptid, count(*) from students where deptid = 'CS' group by deptid
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute the code now to see the output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| <<PAUSE>>

Let us see a few more examples of '''Group by clause.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select gender, count(*) from students group by gender
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute the query.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Point to the output
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| We can see the output as expected.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Now I want to find out the maximum '''CGPA''' score of students from each '''city'''.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select city, count(*), max(CGPA) from students group by city
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| For this, I'll type the query as shown.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Now execute the code to see the output.

<<PAUSE>>

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Likewise, we can make use of any '''aggregate function '''in the '''SELECT statement '''with '''Group by clause.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Next we will see how to use more than one column in '''Group by clause.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code as shown here.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select deptid, city, count(*) from students group by deptid, city

Highlight the column list
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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'''.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute the query.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let us see execute and see the output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Next we will learn about the '''Having clause.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 6(a):'''

'''HAVING clause'''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
* '''HAVING clause''' is used to retrieve rows for the specified condition from a grouped result.
* UseDone the '''WHERE clause '''to exclude rows that you don't want to group, whereas
* UseDone the''' HAVING clause''' to filter rows after they have been grouped.
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 6(b):'''

'''Having'''

'''Syntax:'''

SELECT column1, aggregate-function(column2) FROM table-name [WHERE condition]

GROUP BY column1, column2...

HAVING Condition
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| The syntax is as s

'''SELECT column1, aggregate-function(column2) '''

'''FROM table-name'''

'''[WHERE condition]'''

'''GROUP BY column1 , column2'''

'''HAVING Condition'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let us see an example for this.

Switch to the '''SQL Editor window.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code

Select deptid, city, count(*) from students group by deptid, city

having city = 'Chennai'

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute to see the result.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute and see the result.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Point to the output.

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let’s see another example.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select deptid, city, count(*) from students group by deptid, city

having count(*) > 1
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Click on execute query button.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Once again, execute to see the result.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Next we will see the '''Order by clause.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 7(a):'''

'''Order by '''
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| * '''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.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 7(b):'''

'''Syntax:'''

'''Order by '''

'''SELECT column-list '''

FROM table_name [WHERE condition]

[ORDER BY column1, column2, .. ] [ASC | DESC];
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| The syntax is shown here:

'''SELECT column-list FROM table-name '''

'''[WHERE condition]'''

'''ORDER BY [column1, column2... ]'''

'''[ASC|DESC]'''

|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let us switch back to '''SQL Editor window''' for demonstration.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code

Select deptid, count(*) from students group by deptid order by count(*)
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code as shown on the screen.

I have added the''' Order by''' '''clause''' to the end of the''' SELECT statement.'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code

Select deptid, count(*) from students group by deptid order by count(*) desc
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type '''DESC''' at the end of the query to sort in descending order.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Click on execute query button.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute the query to see the output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| We can also sort result set on multiple columns.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Type the code,

Select studentname, city from students order by city, studentname

Highlight the comma
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select studentname, city from students order by city Desc, studentname Asc
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Let us modify the same query to see '''city''' in descending order and the '''student name '''in ascending order.
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Execute to see the output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| We will see another example of '''order by''' in '''Group by clause.'''

Execute the query to see the output.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Select deptid, city, count(*) from students

group by deptid, city order by deptid, city
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| 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.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| With this we come to the end of this tutorial.

Let us summarize.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Summary
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| In this tutorial, we have learnt about'''missing in slide''' the '''clauses'''done''' '''

* '''Group by '''
* '''Having'''
* '''Order by'''

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Assignment

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| As an assignment-

* Write a '''Select statement '''to display the number of students with the same '''CGPA'''
* Hint: Group the rows by '''CGPA''' column

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Slide 12:

About Spoken Tutorial project

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Slide 13:

Spoken Tutorial workshops

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| The''' Spoken Tutorial Project''' Team

* conducts workshops
* gives certificates

For more details, please write to us.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Slide 13:

Forum for specific questions
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Please post your timed queries in this forum.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Slide 14:

Acknowledgement

| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at

this link.

|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

|}

@@ Line 47: / Line 47: @@
 |-
-| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| done'''Slide 5:'''
+| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 5:'''
 '''Aggregate Functions'''
@@ Line 67: / Line 67: @@
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
 * '''Group by clause '''is used to collect identical data in groups.
-* It returns one change in the sliderecorddone for each group.
+* It returns one record for each group.
 * '''Group by''' can be done with one or more columns.
@@ Line 231: / Line 231: @@
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"|
 * '''HAVING clause''' is used to retrieve rows for the specified condition from a grouped result.
-* UseDone 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
-* UseDone the''' HAVING clause''' to filter rows after they have been grouped.
+* Use the''' HAVING clause''' to filter rows after they have been grouped.
 |-
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Slide 6(b):'''
@@ Line 425: / Line 425: @@
 |-
 | style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| Summary
-| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| In this tutorial, we have learnt about'''missing in slide''' the '''clauses'''done''' '''
+| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| In this tutorial, we have learnt about''''' the '''clauses'''''' '''
 * '''Group by '''

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

Nirmala Venkat at 11:01, 1 August 2018

Nirmala Venkat: Created page with " {| style="border-spacing:0;" | style="background-color:#c0c0c0;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.026cm;padding-right:0.191cm;"| '''Vi..."