ORACLE: Group by clause

Group By clause

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

The SELECT statement used in the GROUP BY clause can only be used contain column names, aggregate functions, constants and expressions.

For example : If we want to extract the total revenue generated by each department of an organisation then we write,

SELECT department_name, SUM(revenue) from organization 

GROUP BY department_name;

General syntax of group by clause is 

SELECT expression 1, expression 2, ....expression n

            aggregate_function (aggregate_expression)

FROM table name

[WHERE conditions]

GROUP BY  expression 1, expression 2, ....expression n

[ORDER BY expressions [ASC |DESC]];

Keywords used : 

SELECT Clause : Is use to extract data from the given table, Although there is    no compulsion in writing them in upper caps but following standards is good!!.

Expressions : A query contains nth number of expressions it means there is no specific limit for that you can write as per your requirements. Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement.

Aggregate_function : These are the Oracle Built-in function used for aggregation such as SUM(), AVG(), MAX(), COUNT(), MIN() etc.

Aggregate_expressions : These are the columns on which aggregation is applied.

WHERE condition : It is optional condition use it when required.

ORDER BY clause : This clause is used for arranging output in ascending or descending order by taking a column.

ASC : Keyword used for arranging in ascending order.

DESC: Keyword for arranging in descending order.

Example using count function : 

To count the number of employees working in a specific department we write,

SELECT department_id, COUNT(1)  as Total_emp from employee

GROUP BY department_id order by department_id ASC;