SQL GROUP BY usage

GROUP by statement
The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns.

SQL GROUP by syntax
SELECT column_name, aggregate_function (column_name)
From table_name
WHERE column_name operator Value
GROUP by column_name

MySQL Tutorial > CREATE TABLE Employee (
   ->     id   & nbsp;        int,
   ->     first_ name    VARCHAR,
   ->     last_name      VARCHAR,
   ->     start_date    Date,
   ->     end_date      date,
    ->     salary        FLOAT (8,2),
    ->     city          VARCHAR (Ten),
   ->     description   VARCHAR (a)
   ->);
Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ', 1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661.78, ' Vancouver ', ' Tester ');
Query OK, 1 row affected (0.02 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (4, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' Vancouver ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (5, ' Robert ', ' black ', ' 19840115 ', ' 19980808 ', 2334.78, ' Vancouver ', ' Tester ');
Query OK, 1 row affected (0.01 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (6, ' Linda ', ' Green ', ' 19870730 ', ' 19960104 ', 4322.78, ' New York ', ' Tester ');
Query OK, 1 row affected (0.02 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897.78, ' New York ', ' Manager ');
Query OK, 1 row affected (0.02 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232.78,null, ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | NULL | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql> SELECT City, COUNT (*) from the employee GROUP by city;
+-----------+----------+
| City | COUNT (*) |
+-----------+----------+
|        NULL | 1 |
|        New York | 2 |
|        Toronto | 1 |
|        Vancouver | 4 |
+-----------+----------+
4 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql>
mysql> drop table Employee;
Query OK, 0 rows Affected (0.00 sec)

Use with Count ()

mysql> SELECT City, COUNT (*) from employee
   -> WHERE description = ' Tester ' OR description = ' Programmer '
   -> GROUP by city;
+-----------+----------+
| city &n bsp;    | COUNT (*) |
+-----------+----------+
| New york  |        1 |
| toronto   |        1 |
| Vancouver |        4 |
+-----------+----------+
3 rows in Set (0.00 sec)

Mysql>
mysql> drop table Employee;
Query OK, 0 rows Affected (0.00 sec)

With are not null

Mysql> SELECT City, COUNT (*) from employee
-> WHERE description is not NULL
-> GROUP by City;
+-----------+----------+
| City | COUNT (*) |
+-----------+----------+
|        New York | 2 |
|        Toronto | 1 |
|        Vancouver | 4 |
+-----------+----------+
3 Rows in Set (0.02 sec)

The most basic

Mysql> SELECT * FROM employee
-> GROUP by City;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
3 rows in Set (0.05 sec)

Note: Aggregate functions, such as SUM, often need to add a GROUP by statement.
The role of the WHERE clause is to remove rows that do not conform to the where condition before grouping the query results, that is, to filter the data before grouping, in conditions that cannot contain a clustered function, and where conditions are used to display a particular row.
The HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including the clustering function, using the having condition to display a specific group, or multiple grouping criteria for grouping.