GROUP BY Clause

GROUP BY clause is used to group identical data.

                           

Note

GROUP BY clause must follows the WHERE clause and precede the ORDER BY clause.

Syntax

SELECT column_list

FROM table_name

WHERE condition

[GROUP BY column]

[ORDER BY column]

Sample Data

CREATE TABLE employee(

  id int PRIMARY KEY,

  name VARCHAR(25),

  age int,

  salary DECIMAL(10, 4),

  mailId VARCHAR(25),

  city VARCHAR(10)

);

INSERT INTO employee VALUES (1, "Hari", 28, 12345.67, "hari@hari.com", "Bangalore");

INSERT INTO employee VALUES (2, "Sandesh", 30, 98345.00, "sandesh@sandesh.com", "Trivendram");

INSERT INTO employee VALUES (3, "Phalgum", 33, 119345.67, "phalgun@hari.com", "Hyderabad");

INSERT INTO employee VALUES (4, "Manju", 30, 87666.87, "manju@sandesh.com", "Bangalore");

INSERT INTO employee VALUES (5, "Rakesh", 26, 38000, "rakesh@hari.com", "Bangalore");

INSERT INTO employee VALUES (6, "Sankalp", 38, 87645.67, "sankalp@sankalp.com", "Chenai");

INSERT INTO employee VALUES (7, "Vadiraj", 28, 12345.67, "vadi@hari.com", "Bangalore");

INSERT INTO employee VALUES (8, "Prasob", 28, 12345.67, "prasob@sandesh.com", "Trivendram");

INSERT INTO employee (id, name, salary) VALUES (9, "Kesav", 123457.89);

INSERT INTO employee VALUES (10, "Hari", 25, 198345.00, "hari@krishna.com", "Trivendram");

INSERT INTO employee VALUES (11, "Phalgun", 23, 119345.67, "phalgun@p.com", "Hyderabad");

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   30 |  98345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   30 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  38000.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   28 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   28 |  12345.6700 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 123457.8900 | NULL                | NULL       |
| 10 | Hari    |   25 | 198345.0000 | hari@krishna.com    | Trivendram |
| 11 | Phalgun |   23 | 119345.6700 | phalgun@p.com       | Hyderabad  |
+----+---------+------+-------------+---------------------+------------+
11 rows in set (0.00 sec)

a. Get count of employees staying in different cities

mysql> SELECT city, COUNT(*) FROM employee GROUP BY city;
+------------+----------+
| city       | COUNT(*) |
+------------+----------+
| NULL       |        1 |
| Bangalore  |        4 |
| Chenai     |        1 |
| Hyderabad  |        2 |
| Trivendram |        3 |
+------------+----------+
5 rows in set (0.00 sec)

b. Get count of employees by age

mysql> SELECT age, COUNT(*) FROM employee GROUP BY age;
+------+----------+
| age  | COUNT(*) |
+------+----------+  
| NULL |        1 |
|   23 |        1 |
|   25 |        1 |
|   26 |        1 |
|   28 |        3 |
|   30 |        2 |
|   33 |        1 |
|   38 |        1 |
+------+----------+
8 rows in set (0.00 sec)

Previous                                                 Next                                                 Home