MySQL - GROUP BY Clause

Table of content

MySQL GROUP BY Clause

The GROUP BY clause in MySQL is used to arrange identical data in a table into groups.

For example, let us suppose you have a table of sales data of an organization consisting of date, product, and sales amount. To calculate the total sales in a particular year, the GROUP BY clause can be used to group the sales of products made in that year. Similarly, you can group the data by date to calculate the total sales for each day, or by a combination of product and date to calculate the total sales for each product on each day.

This GROUP BY clause follows the WHERE clause in an SQL statement and precedes the ORDER BY or HAVING clause (if they exist). You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. You can use COUNT, SUM, AVG, etc., functions on the grouped column.

Syntax

Following is the basic syntax to use GROUP BY with SELECT statement −

SELECT column_name(s) FROM table_name
GROUP BY [condition | column_name(s)];

Example

This example demonstrates how to use aggregate functions with GROUP BY clause.

First of all, create a table named CUSTOMERS, using the following CREATE TABLE query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert the following records into the CUSTOMERS table using the following INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table is created as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, use the following GROUP BY query to group the customers based on their age −

SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

Output

Following is the result produced −

AGE COUNT(Name)
32 1
25 2
23 1
27 1
22 1
24 1

MySQL GROUP BY on Single Column

When we use the GROUP BY clause on a single column, all common values in that column will be added together making it a single record.

Example

In this example, let us group the customers by their age and calculate the average salary for each age using the following query −

SELECT AGE, AVG(SALARY) AS AVG_SALARY 
FROM CUSTOMERS 
GROUP BY AGE;

Output

This would produce the following result −

AGE AVG_SALARY
32 2000.000000
25 4000.000000
23 2000.000000
27 8500.000000
22 4500.000000
24 10000.000000

MySQL GROUP BY on Multiple Columns

When we use the GROUP BY clause with multiple columns, the common record obtained by combining values from these columns will be grouped together into a single record.

Example

In this example, if you want to know the total amount of salary for each customer age wise, then the GROUP BY query would be as follows −

SELECT CONCAT(AGE, ' - ', SALARY) AS SALARY_AGEWISE 
FROM CUSTOMERS 
GROUP BY AGE, SALARY;

Output

This would produce the following result −

SALARY_AGEWISE
32 - 2000.00
25 - 1500.00
23 - 2000.00
25 - 6500.00
27 - 8500.00
22 - 4500.00
24 - 10000.00

MySQL GROUP BY with ORDER BY Clause

We can use the ORDER BY clause with GROUP BY in MySQL to sort the result set by one or more columns.

Syntax

Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL −

SELECT column1, column2, ..., aggregate_function(columnX) AS alias
FROM table
GROUP BY column1, column2, ...
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example

In here, we are trying to find the highest salary for each age, sorted by high to low −

SELECT AGE, MAX(salary) AS MAX_SALARY 
FROM CUSTOMERS 
GROUP BY AGE 
ORDER BY MAX(salary) DESC;

Output

This would produce the following result −

AGE AVG_SALARY
24 10000.00
27 8500.00
25 6500.00
22 4500.00
32 2000.00
23 2000.00

MySQL GROUP BY with HAVING Clause

We can also use the GROUP BY clause with the HAVING clause to filter the results of a query based on conditions applied to groups of data. The condition can be applied to an aggregate function that is used in the SELECT statement or to a column in the GROUP BY clause.

Syntax

Following is the syntax for using ORDER BY clause with HAVING clause in SQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

Example

In the following query, we are grouping the customers by their age and calculating the average salary for each group. The HAVING clause is used to filter the results to show only those groups where the average salary is greater than 8000 −

SELECT AGE, AVG(SALARY) AS AVG_SALARY 
FROM CUSTOMERS 
GROUP BY AGE HAVING AVG(salary) > 8000;

Output

This would produce the following result −

AGE AVG_SALARY
27 8500.000000
24 10000.000000

GROUP BY Clause Using Client Program

In addition to using GROUP BY Clause in MySQL server with an SQL query, we can also execute the GROUP BY clause using a client program.

Syntax

Following are the syntaxes of the Group by Clause in select statement in various programming languages −

PHP NodeJS Java Python

To use GROUP BY Clause in MySQL table through PHP program, we need to execute the SQL statement using the function named query() provided by mysqli connector −

$sql = "SELECT EXPRESSION1, EXPRESSION2, ... EXPRESSION_N,
   AGGREGATE_FUNCTION(EXPRESSION) FROM TABLE_NAME
   [WHERE CONDITION] GROUP BY EXPRESSION1, EXPRESSION2..";
$mysqli->query($sql,$resultmode)

To use GROUP BY Clause in MySQL table through JavaScript program, we need to execute the SQL statement using the function named query() provided by mysql2 connector −

sql= " SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)];"
Con.query(sql);

To use GROUP BY Clause in MySQL table through Java program, we need to execute the SQL statement using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT column_name(s) FROM table_name
   GROUP BY [condition | column_name(s)]";
statement.executeQuery(sql);

To use GROUP BY Clause in MySQL table through Java program, we need to execute the SQL statement using the function named execute() provided by MySQL Connector/Python −

group_by_clause_query = "SELECT column_name(s) aggregate_function(column)
   FROM table_name GROUP BY column_name(s)"
cursorObj.execute(group_by_clause_query)

Example

Following are the implementations of GROUP BY using various programming languages −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = 'SELECT tutorial_title, count(tutorial_id) AS tot_count FROM tutorials_tbl WHERE tutorial_id > 2 GROUP BY tutorial_title';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("Title: %s, Count: %d",  
         $row["tutorial_title"],
         $row["tot_count"]);
      printf("\n");         
   }
} else {
   printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Title: JAVA Tutorial, Count: 1
Title: Learn PHP, Count: 1
Title: Learn MySQL, Count: 2

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, 'John', '2007-01-24', 250), (2, 'Ram', '2007-05-27', 220), (3, 'Jack', '2007-05-06', 170), (3, 'Jack', '2007-04-06', 100), (4, 'Jill', '2007-04-06', 220),(5, 'Zara', '2007-06-06', 300),(5, 'Zara', '2007-02-06', 350);"
  con.query(sql);

  //Using GROUP BY Clause
  sql = "SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { name: 'John', 'COUNT(*)': 1 },
  { name: 'Ram', 'COUNT(*)': 1 },
  { name: 'Jack', 'COUNT(*)': 2 },
  { name: 'Jill', 'COUNT(*)': 1 },
  { name: 'Zara', 'COUNT(*)': 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class GroupByClause {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT AGE, AVG(SALARY) as avg_salary FROM CUSTOMERS GROUP BY age";
            rs = st.executeQuery(sql);
            System.out.println("Table records(gruop by age): ");
            while(rs.next()){
            	String age = rs.getString("Age");
            	String avg_salary = rs.getString("avg_salary");
            	System.out.println("Age: " + age + ", Salary: " + avg_salary);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Output

The output obtained is as shown below −

Table records(gruop by age): 
Age: 32, Salary: 2000.000000
Age: 25, Salary: 4000.000000
Age: 23, Salary: 2000.000000
Age: 27, Salary: 8500.000000
Age: 22, Salary: 4500.000000
Age: 24, Salary: 10000.000000

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
group_by_clause_query = """
SELECT ADDRESS, AVG(SALARY) AS average_salary
FROM customers
GROUP BY ADDRESS
"""
cursorObj.execute(group_by_clause_query)
# Fetching all the grouped rows
grouped_rows = cursorObj.fetchall()
for row in grouped_rows:
    address, average_salary = row
    print(f"Address: {address}, Average Salary: {average_salary}")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Address: Hyderabad, Average Salary: 7500.000000
Address: Kerala, Average Salary: 8000.000000
Address: Mumbai, Average Salary: 1200.000000
Address: Vishakapatnam, Average Salary: 8500.000000
Address: Delhi, Average Salary: 10000.000000
Advertisements