SQL group by usage

Group by statement
The group by statement is used in combination with the aggregate function 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 int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
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', '000000', '000000', 19960725, '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', '000000', '000000', 19760321, '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', '000000', '000000', 19781212, '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, 'cela', 'Rice ', '000000', '000000', 19821024, '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', '000000', '000000', 19840115, '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', '000000', '000000', 19870730, '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', '123', '123', 19901231, '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', '000000', '000000', 19960917, 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 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 4322.78 | New York | Tester |
| 7 | David | Larry | 7897.78 | New York | Manager |
| 8 | James | Cat | 1232.78 | NULL | Tester |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql> SELECT city, COUNT (*) FROM 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)

Used with count ()

Mysql> SELECT city, COUNT (*) FROM employee
-> WHERE description = 'tester' OR description = 'programmer'
-> Group by city;
+ ----------- + ---------- +
| City | 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)

Is 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 | 4322.78 | New York | Tester |
| 1 | Jason | Martin | 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: a group by statement is often required for Aggregate functions (such as SUM.
The where clause is used to remove rows that do not meet the where condition before grouping query results. That is, data is filtered before grouping. The condition cannot contain clustering functions, use the where condition to display specific rows.
The having clause is used to filter groups that meet the conditions. That is, data is filtered after the group. conditions often contain clustering functions and the having condition is used to display specific groups, you can also use multiple grouping standards for grouping.