Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Group By Clause

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

GROUP BY Clause
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 
PHPNodeJSJavaPython
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)
Example
Following are the implementations of GROUP BY using various programming languages 
PHPNodeJSJavaPython
$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
P
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

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

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

GROUP BY Clause

MySQL GROUP BY Clause

Syntax

Example

Output

MySQL GROUP BY on Single Column

Example

Output

MySQL GROUP BY on Multiple Columns

Example

Output

MySQL GROUP BY with ORDER BY Clause

Syntax

Example

Output

MySQL GROUP BY with HAVING Clause

Syntax

Example

Output

GROUP BY Clause Using Client Program

Syntax

Example

Output