The SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of employee in each country".

TheThe GROUP BY statement is often used with aggregate functions (The COUNT() ,The MAX() ,The MIN() ,The SUM() ,The AVG() ) to group the result-set by one or more columns.

Syntax

 SELECT column_name(s)
 FROM  table_name 
 WHERE  condition 
 GROUP BY column_name(s)
 ORDER BY  column_name(s) ;

You can click on above box to edit the code and run again.

Demo Employee table

This employee table is used for examples:

SQL GROUP BY Examples

The following SQL statement lists the number of employee in each country:

Example

 SELECT COUNT(Employee_id),Country
 FROM employee 
 GROUP BY Country;

You can click on above box to edit the code and run again.

Output

The following SQL statement lists the number of employee in each country, sorted high to low:

Example

 SELECT COUNT(Employee_id),Country
  FROM employee 
              
 GROUP BY Country
 ORDER BY COUNT (employee_id)   DESC ;

You can click on above box to edit the code and run again.

Output

Demo Products table

This product table is used in examples:

And a selection from the "Supplier" table:

demo supplier table

GROUP BY With JOIN Example

The following SQL statement lists the number of orders sent by each suppliers:

Example

 SELECT  supplier.Supplier_name,  COUNT  (products.Product_id)   AS   NumberOfOrders
 FROM products
 LEFT JOIN  Supplier   ON  products.Supplier_id = supplier.Supplier_id
 GROUP BY  Supplier_name;

You can click on above box to edit the code and run again.

Output