Page 1 - Grouping Records and Join in SQL

SQL Group by and Join Commands

GROUP BY clause
(returns a single value for a set of specified rows)

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and
group the results by one or more columns.
It groups the rows on the basis of the values present in one of the columns and then the aggregate
functions are applied on any column of these groups to obtain the result of the query.
Syntax:
select <col1, col2, …, colN>, <aggregate/statistical function>
from <tables>
where <conditions>
group by <col1, col2, …, colN>

Set of columns with select has to be same as with group by.

GROUP BY clause
Select deptno, count(*), max(sal)
from employee
GROUP BY deptno;

DEPTNO COUNT(*) MAX(SAL)
30 6 2850
20 5 3000
10 3 5000

In a select statement containing GROUP BY clause only statistical function and the attribute on
which groups are formed can be used.

select deptno, job, count(*)
from employee
group by deptno, job;

Group by with order by
select deptno, job, count(*)
from employee
group by deptno, job
order by deptno, job;