GROUP BY

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc)  to retrieve data grouped according to one or more columns.

Syntax:

The basic syntax of GROUP BY clause is given below.

SELECT column1, column2

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

NP: Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:
SELECT NAME AS EMP_NAME, COUNT(*) AS CNT

FROM EMPLOYEE1

GROUP BY EMP_NAME

Correct way is:
SELECT NAME AS EMP_NAME, COUNT(*) AS CNT

FROM EMPLOYEE1

GROUP BY NAME

Example:

Here is the EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	12,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	11,000	PUNE
8	A	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	GOA
8	B	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	DELHI

1)Find the total amount of salary on each employee, then GROUP BY query would be as follows:

SELECT NAME, SUM(SALARY) FROM EMPLOYEE1

     GROUP BY NAME;

This would produce the following result:

NAME	SUM(SALARY)
D	10,000
A	20,000
B	20,000
C	10,000
E	12,000
F	15,000
G	11,000

Restriction on SELECT Lists with Aggregation
If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.