GROUP BY clause

GROUP BY clause

You can use the GROUP BY clause to divide the rows in a table into groups

syntax:

select column,group_function(column)

from table

[where condition]

[GROUP BY group_by_expression]

[ORDER BY column];

Guidelines:


* Using a where clause ,you can exclude rows before dividing then into groups.

* You can not use a column alias in the GROUP BY clause

* BY default,rows are sorted by ascending order of the columns included in the GROUP BY list. You can override this by using the ORDER BY clause


example:

select department_id,AVG(salary)

from employees

group by department_id;


* The GROUP BY column does not have to be in the select clause


example:

select AVG(salary)

from employees

group by department_id;

* grouping by more than one column

* sometimes we need to see results for groups within groups.

for example if you want to see total salary being paid to each job title,within each department.


select department_id,job_id,SUM(salary)

from employees

GROUP BY department_id,job_id;

Illegal Queries Using Group Functions


* Any column or expression in the select list that is not an aggregate function must be in the GROUP BY clause.


select department_id,COUNT(last_name)

from employees;

select department_id,count(last_name)

*
ERROR at line 1:

ORA-00937: not a single-group group function

* Whenever you use a mixture of individual items(DEPARTMENT_ID) and group functions(COUNT) in the same SELECT statement,you must include a GROUP BY clause that specifies the individual items(in this case, DEPARTMENT_ID).


select department_id,count(last_name)

from employees

group by department_id;


* Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.


* You can not use group function in the where clause


* Use the HAVING clause to restrict groups


select department_id,avg(salary)
from employees
having avg(salary)>8000
group by department_id;