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

Aggregation facilities in SQL

Learning Objectives

• Group by
• Having
• Order by clause

• Group by
• Having and
• Order by clause

System requirement

• Ubuntu Linux 14.04 operating system
• PostgreSQL 9.3.x
• pgAdmin 1.18
  

Pre-requisites

• SELECT statement and
• Aggregate functions.

• For more details, refer to the previous RDBMS PostgreSQL tutorials on this website.

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.

We learnt about these functions in the earlier tutorials.

Aggregate functions are mostly used with the Group By clause of the SELECT statement.

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.

GROUP BY

Syntax:

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

GROUP BY column1, column2...

SELECT column1, aggregate-function of column2

FROM table-name

[WHERE condition]

GROUP BY column1 , column2

Select studentname, city, cgpa, deptid from students

How can we group these records based on a column?

First clear the SQL editor window.

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.

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

Select deptid, count(*) from students where deptid = 'CS' group by deptid

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.

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

So, we have to group the rows with the column gender.

<<PAUSE>>

<<PAUSE>>

Highlight the column list

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.

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.

Having

Syntax:

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

GROUP BY column1, column2...

HAVING Condition

SELECT column1, aggregate-function(column2)

FROM table-name

[WHERE condition]

GROUP BY column1 , column2

HAVING Condition

Switch to the SQL Editor window.

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

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.

There are 3 students from CS department and 1 student from EE department.

The condition specifies that the students are from Chennai.

having count(*) > 1

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.

Order by

• This clause is always used at the end of the SELECT statement.

Syntax:

Order by

SELECT column-list

FROM table_name [WHERE condition]

[ORDER BY column1, column2, .. ] [ASC | DESC];

SELECT column-list FROM table-name

[WHERE condition]

ORDER BY [column1, column2... ]

[ASC|DESC]

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

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

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.

Select deptid, count(*) from students group by deptid order by count(*) desc

Select studentname, city from students order by city, studentname

Highlight the comma

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.

Execute the query to see the output.

group by deptid, city order by deptid, city

Dept id is sorted in ascending order and within each department, the city is sorted in ascending order.

Let us summarize.

• Group by
• Having
• Order by

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

About Spoken Tutorial project

Please download and watch it.

Spoken Tutorial workshops

• conducts workshops
• gives certificates

For more details, please write to us.

Forum for specific questions

Acknowledgement

More information on this mission is available at

this link.