UXDE dot Net
Advertisement

MySQL Tutorial: GROUP BY Clause

By -

This tutorial is about how to use MySQL clause GROUP BY. For more tutorials in MySQL visit MySQL Tutorials

GROUP BY is used in conjunction with an any aggregate function of MySQL. e.g SUM(), COUNT() etc

By the help of this keyword, you are able to make groups in one table according to one column, or even more.

Syntax:

SELECT column, AGGREGATE_FUNTION(*)
FROM table
WHERE required condition
GROUP BY column

GROUP BY sorts a result on the basis of that column and then aggregates it value based on another column. Following are the examples of MySQL aggregate functions

  • SUM
  • AVG
  • MAX
  • MIN
  • COUNT

I would show you some of the examples to make it clarified.

You are having a table in your database like this

first_name gender age date_registered
 1  Bob  Male  23  2012-12-15 09:56:42
 2  Anna  Female  21  2012-12-16 10:26:02
 3  Dan  Male  19  2012-12-17 11:54:22
 4  Agatha  Female  17  2012-12-18 20:56:02
 5  John  Male  30  2012-12-19 14:46:43
 6  Daniel  Male  21  2012-12-20 10:56:02

 

Let us see some examples:

GROUP BY one column:

Query:
SELECT gender, COUNT(*)
FROM employee_data
WHERE date_registered < NOW()
GROUP BY gender

 

Result:

mysql_tutorial_groupby_one

Hence by grouping it out based on gender, we know that we have in the company:

  • Two female employees and
  • Four male employees

 

GROUP BY two columns:

We can also do something like grouping them based on two columns. It makes the result contain more information. It is to make sure that grouping by two columns means that in the result set both of the columns other than your aggregate functions are to be shown.

Query:
SELECT gender, age, COUNT(*)
FROM employee_data
WHERE date_registered < NOW()
GROUP BY gender, age

 

Result:

mysql_tutorial_groupby_two_columns

 

Grouping by two columns; age and gender showed us a lot many information about both the gender and the age of employees and how they are inter-related. For-example here:

  • Two female employees are there and both are of age 21.
  • Four male employee are there, in which 2 are of age 21
  • Third one is of age 19 and the forth one is of age 23.

This is how GROUP BY clause arranges the data so as to give a useful meaning.

Farheen Bibi

You can find Farheen on , and .

Leave a Reply