MySQL GROUP BY

The GROUP BY clause is used to group rows with the same values, and is used in SELECT statement with aggregation functions such as COUNT, SUM, MIN, MAX, AVG, etc. and returns only one row for each grouped item.
 

Syntax:

SELECT column1, column2, ... column_n, 
aggregate_function (column)
FROM tables
[WHERE conditions]
GROUP BY column1, column2, ... column_n;

 

 

GROUP BY clause with COUNT function

Let’s consider the “Clients” table, having the following records.
 

 
Now let’s count the number of duplicate address.

SELECT Address, COUNT(*)
FROM Clients 
GROUP BY Address;

 

GROUP BY clause with SUM function

Consider the “Employee” table, with the following data.
 

 
Now the following query will group records using SUM function and return the name and total number of hours worked for each employee.

SELECT Name, SUM(working_hours) AS "Total working hours"
FROM Employee
GROUP BY Nom;

 

 

GROUP BY clause with MIN function

The following example displays the minimum working hours for employees.

SELECT Name, MIN(working_hours) AS "Minimum working hour"
FROM Employee
GROUP BY Name;

 

GROUP BY clause with MAX function

The following example displays the maximum working hours for employees.

SELECT Name, MAX(working_hours) AS "Maximum working hour"
FROM Employee
GROUP BY Name;

 

 

GROUP BY clause with AVG function

The following example displays the average working hours of employees.

SELECT Name, AVG(working_hours) AS "Average working hours"
FROM Employee
GROUP BY Name;

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More