15 - SQL GROUP BY CLAUSE

 

 In this chapter you would learn to group identical data, like pivots in excel.

As the name suggests, GROUP BY clause is used to group the results depending on one or more columns.  GROUP BY is given after the WHERE conditions and before the ORDER BY clause in the SELECT statement.

Syntax:

SELECT COL1, COL2,…COLN FROM table_name

WHERE conditions

GROUP BY COL1, COL2

ORDER BY COL1,COL2;

Please note that column names mentioned in the GROUP BY clause should be present in the column list in SELECT statement.

Consider the tables EMPLOYEE and DEPARTMENTS.

EMPLOYEE:

+-----------+--------------------+-----------+---------------------+---------------+------------------+
| EMP_ID | EMMP_NAME | AGEG     | PHONE_NUM | DEPT_ID     | SALARY   |
+-----------+--------------------+-----------+---------------------+---------------+------------------+
|      1 | john      |   35     | 100233023 |       2     |  10000   |
|      2 | Linda     |   30     | 100234565 |       1     |  15000   |
|      3 | Max       |   40     | 122222344 |       3     |  22000   |
|      4 | Will      |   40     |  12323424 |       3     |  31000   |
|      5 | Michal    |   45     |  12323434 |       3     |   5000   |
+-----------+--------------------+-----------+---------------------+---------------+------------------+

DEPARTMENTS:

+------------+-----------------------+
| DEPT_ID | DEPT_NAME  |
+-----------+-----------------------+
|       1 | Accounts   |
|       2 | HR         |
|       3 | Production |
+------------+-----------------------+

Example1:

Get the max salary for employees in each department. 

mysql> SELECT DEPT_ID, SUM(SALARY) AS SALARY

    -> FROM EMPLOYEE

    -> GROUP BY DEPT_ID;

This will give the max salary for each department.

+------------+-------------+
| DEPT_ID | SALARY     |
+------------+-------------+
|      1  |  15000     |
|      2  |  10000     |
|      3  |  58000     |
+------------+-------------+
3 rows in set (0.06 sec)

Example 2:

What will be output of the below query?

mysql> SELECT EMP_ID, DEPT_ID, SUM(SALARY) AS SALARY

    -> FROM EMPLOYEE

    -> GROUP BY EMP_NAME;

Solution:

Output would be error statement as it is expected that the column name mentioned in GROUP BY should be present in column list in SELECT statement.

ERROR 1054 (42S22): Unknown column 'EMP_NAME' in 'group statement'

Example 3:

Suppose a sales manager wants to know the average sale for each salesperson under him then we can use the AVG function and GROUP BY as below: 

 

SELECT SALESREP, AVG(SALES)

FROM SALES_DETAILS

GROUP BY SALESREP;

 In the above example average salary for each department can be given as :

 

mysql> SELECT DEPT_ID, AVG(SALARY)

    -> FROM EMPLOYEE

    -> GROUP BY DEPT_ID;

+-----------------+----------------------------+

Example 4:

Suppose we have to get count of employees with salary > 10000 for each department, for this we can use COUNT function and GROUP BY as below.

mysql> SELECT DEPT_ID, COUNT(EMMP_NAME) AS EMP
    -> FROM EMPLOYEE
    -> WHERE SALARY > 10000
    -> GROUP BY DEPT_ID;
+-------------------+--------------------+
| DEPT_ID     | EMP         |
+-------------------+--------------------+
|       1     |            1|
|       3     |            2|
+--------------------+-------------------+
2 rows in set (0.06 sec)

Whenever ORDER BY is given along with GROUP BY clause, then the data are first arranged in groups and then ORDER BY sorts the rows within each group.

Example 5:

In the above example 3, we had just grouped the average salary on department, Now we need to sort the results based on SALARY in ascending order.

mysql> SELECT DEPT_ID, AVG(SALARY) AS SAL
    -> FROM EMPLOYEE
    -> GROUP BY DEPT_ID
    -> ORDER BY SAL;
+------------------+-----------------+
| DEPT_ID     | SAL         |
+------------------+-----------------+
|       2     | 10000.0000  |
|       1     | 15000.0000  |
|       3     | 19333.3333  |
+------------------+-----------------+
3 rows in set (0.23 sec)

Like us on Facebook