Difference between group by clause and order by clause

In a single sentence we can differentiate group by clause and order by clause as follows..

group by: This clause is used to group the records of a table based on their common properties and then we apply group functions such as sum(), max(), min(), count() on the groups.

order by: This clause is used to sort ( either ascending or descending ) the records based on a field or column of the table

EXPLANATION ( group by )

Group by clause is used to group the rows based on their common properties. For example, we can group the records of employee table by department, by manager, by job etc.,

See the following table….

If we apply grouping on departments column, the records will be grouped based on departments as follows..

If we apply grouping on Manager column, the records will be arranged as follows..

Advantages of grouping:

We can apply group functions such as sum(), max(), min(), count()  on  groups  to find total salary being drawn by each and every group, we can find maximum salary for each group, we can find number of employees have been in each and every group, etc..

For example let us try to find total salary being drawn by each  and every departments..

Select  department, sum(Sal)  from EMPLOYEE group by department;

When we execute the above query, the execution will be done in the following steps

1)      1)The records will be grouped based on departments

2)      2)Totals will be calculated for each and every group

3)      3)One record will be returned from  each group as a result

See the following transitions…

Do's and Dont's:

1) All the columns that have been written after "select" clause must appear in group by clause

Ex:  select  department, job, sum(sal)  from employee group by department;

the above query is wrong, because the column "job" was not included after group by clause. the correct is...

       select  department, job, sum(sal)  from employee group by department,job;

2) whatever the column we use in group function, that need not to appear in group by clause. In the above   query the column "sal"  has been used in sum( ) function. Here, the column "sal" is not necessary to be appeared in the group by clause.

EXPLANATION ( order by )

order by clause can only be used to sort the records based on values of a column and we cant apply any group functions. There are two types of sortings, 1) Ascending  2) Descending

Ascending: This is the default order of order by clause. This method arranges elements in A to Z(0-9) order

select * from employee order by ename;

Descending : This method arranges elements in Z to A (9 to 0) order.

select * from employee order by ename desc;