×
☰ See All Chapters

MySQL GROUP BY Clause

  • The MYSQL GROUP BY Clause is used to in association with the SELECT statement to arrange identical data into groups by one or more column. 

  • GROUP BY clause also orders the results by the specified column, means GROUP BY clause groups and orders the results. So unless you need to order the values in a different pattern than the GROUP BY clause, the ORDER BY clause is redundant. 

  • Always aggregate functions will be applied on groups, means GROUP BY clause uses aggregate functions AVG, MAX, MIN, SUM, and COUNT to arrange data in groups. 

  • Columns selected for GROUP BY clause must be in SELECT clause. If a column is not in the SELECT statement, you cannot use it in the GROUP BY clause. When grouping the data, the order of columns grouped does not have to match the column order in the SELECT clause. 

  • The position of the GROUP BY clause in a query is as follows: 

SELECT

FROM

WHERE

GROUP BY

ORDER BY

MySQL GROUP BY Clause syntax

SELECT COLUMN1, COLUMN2

FROM TABLE

WHERE <Condition>

GROUP BY COLUMN1, COLUMN2

Like the ORDER BY clause, you can order the GROUP BY clause by using an integer to represent the column name.

SELECT COLUMN1, COLUMN2

FROM TABLE

WHERE <Condition>

GROUP BY 1, 2

Following steps happens for above query

  1. Order the entire TABLE considering COLUMN1 (Ascending order, 0-9 for numeric, A-Z for alphabets). While ordering if same data is found in COLUMN1 then pre-existing order of TABLE is used for same data. 

  2. If same data is present in COLUMN1 (column of GROUP BY clause) group them, which mean do partition the TABLE where each partition will be having same data in all rows of COLUMN1 in their own partition. 

  3. Now perform SELECT COLUMN1, COLUMN2 on partitions separately. This select statement will return only one row for each partition, which will be the first row of each partition. If COLUMN1 or COLUMN2 involves aggregate functions then the row which results from aggregate function will be given. 

mysql-group-by-clause-0
 

MySQL GROUP BY Clause examples

Creating table for demonstrating GROUP BY clause

CREATE TABLE EMP

(EMPNO NUMERIC(4) ,

ENAME VARCHAR(10),

JOB VARCHAR(9),

MGR NUMERIC(4),

HIREDATE DATE,

SAL NUMERIC(7,2),

CONSTRAINT PK_EMP PRIMARY KEY(EMPNO)

);

 

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-5-23',1100);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300);

 

COMMIT;

Example 1

SELECT JOB, COUNT(*),ENAME

FROM EMP

GROUP BY JOB

mysql-group-by-clause-1

Example 2

SELECT ENAME,EMPNO,COUNT(*)

FROM EMP

GROUP BY  EMPNO, ENAME

mysql-group-by-clause-2
 

Example 3

SELECT SUM(SAL),JOB

FROM EMP

GROUP BY JOB

mysql-group-by-clause-3

Example 4

SELECT JOB,COUNT(*)

FROM EMP

GROUP BY JOB

mysql-group-by-clause-4

 

 

 

 

All Chapters
Author