Group by Clause:




The group clause also exists in the SELECT statement. The functionality of the Group by clause is very different from the FROM clause and WHERE clause.

It can be seen from the name of the Group by Clause that the Group by clause is used to group up together a type of information and then summarize the related data. The group by clause can be included into the SELECT statement whether a WHERE clause is used or not.




The group by clause is used to group the data by column values. Another clause name the HAVING clause can also be used with the group by clause to filter the data in groups. It is same as the WHERE clause the only difference is that the WHERE clause filter the raw data and the having clause filter the group data.

 

SYNTAX

SELECT column-name, function (column-name)

FROM table-name

WHERE condition

Group by column-name

EXAMPLE

Suppose you have a table that is named STUDENT as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Let we want to find the id and the name of the student based on their monthly fee, then we will use the following query:

SELECT STUD_ID, name

FROM Student GROUP BY MONFEE

The above query will produce the following result:

STUD_ID NAME
100 Tim
100 Faddy
140 Jones




EXAMPLE of GROUP BY with WHERE clause:

Suppose you have a table that is named STUDENT as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Let you want to group the data on a specific condition for example you want to display the name and the id of those students whose monthly fee is greater than 1000, then you will use the WHERE statement in your query. Then the statement will be as follows:

SELECET name, STUD_ID, MONFEE

FROM student

WHERE MONFEE > 1000

Group by MONFEE

This query will generate the following result:

STUD_ID NAME MONFEE
140 Jones 1200
110 John 1100
110 James 1100
190 Abraham 1200




Therefore, the name and id is displayed of those students whose monthly fee is greater than 1000. It should be noted here that the group by clause came at the end of the query. It is mandatory to write it at the end just like the Order by clause.