Tutorials Point


  Learning MySQL
  MySQL Functions
  MySQL Useful Resources
  Selected Reading

© 2011 TutorialsPoint.COM


  Home     References     Discussion Forums     About TP  

MySQL GROUP BY Clause


previous AddThis Social Bookmark Button


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.


previous Printer Friendly



  

Advertisement

Online Image Processing

Indian Baby Names