Web | This Site

Learning MySQL
MySQL Functions
MySQL Useful Resources
Selected Reading
© 2011 TutorialsPoint.COM

| Home |  | References |  | Discussion Forums |  | About TP

# MySQL GROUP BY Clause

You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. You can use COUNT, SUM, AVG etc function on the grouped column.

To understand GROUP BY clause consider an employee_tbl table which is having following records:

    mysql> SELECT * FROM employee_tbl;
    +------+------+------------+--------------------+
    | id   | name | work_date  | daily_typing_pages |
    +------+------+------------+--------------------+
    |    1 | John | 2007-01-24 |                250 |
    |    2 | Ram  | 2007-05-27 |                220 |
    |    3 | Jack | 2007-05-06 |                170 |
    |    3 | Jack | 2007-04-06 |                100 |
    |    4 | Jill | 2007-04-06 |                220 |
    |    5 | Zara | 2007-06-06 |                300 |
    |    5 | Zara | 2007-02-06 |                350 |
    +------+------+------------+--------------------+
    7 rows in set (0.00 sec)

Now suppose based on the above table we want to count number of days each employee did work.

If we will write a SQL query as follows then we will get following result:

    mysql> SELECT COUNT(*) FROM employee_tbl;
    +---------------------------+
    | COUNT(*)                  |
    +---------------------------+
    | 7                         |
    +---------------------------+

But this is not serving our purpose, we want to display total number of pages typed by each person separately. This is done by using aggregate functions in conjunction with a GROUP BY clause as follows:

    mysql> SELECT name, COUNT(*)
        -> FROM   employee_tbl
        -> GROUP BY name;
    +------+----------+
    | name | COUNT(*) |
    +------+----------+
    | Jack |        2 |
    | Jill |        1 |
    | John |        1 |
    | Ram  |        1 |
    | Zara |        2 |
    +------+----------+
    5 rows in set (0.04 sec)

We will see more functionality related to GROUP BY in other functions like SUM, AVG etc.

Advertisement

Online Image Processing

Indian Baby Names