What is GROUP BY?

The GROUP BY clause is a SQL statement that is used to group rows that have the same values. GROUP BY clause only return a single row for every grouped item. MySQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING, SELECT, DISTINCT, ORDER BY and LIMIT clauses:

syntax :-

select expression1,expression2,expression3,aggregate_function(expression) from table [where condition] GROUP BY exp1,exp2,exp_n;

The GROUP BY clause can group the result set by one or more columns. That means the Group by Clause divides the similar type of records or data as a group and then returns. If we use group by clause in the query then we should use grouping/aggregate function such as count(), sum(), max(), min(), avg() functions. aggregate_function: The aggregate function can be SUM, COUNT, MIN, MAX, or AVG functions that we should use while we are using the Group by Clause in MySQL. WHERE conditions: It is optional. If you want to retrieve the data based on some conditions then you need to specify such conditions using the Where Clause. NOTE :- The GROUP BY clause in MySQL should be placed after the FROM and WHERE clauses. Again there must be at least one column or one expression after the GROUP BY keyword, that you want to use as criteria to group the records or rows.

Example

CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(32) NOT NULL,
`salary` double NOT NULL,
`address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`id`, `name`, `salary`, `address`) VALUES
(1, 'mahtab', 59000, 'Ara'),
(2, 'Sana', 345245, 'ara'),
(3, 'Mahira', 45342, 'Dhandiha'),
(4, 'Sara', 53423, 'Patna'),
(5, 'Arham', 34523, 'Delhi');

The MySQL GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns. You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc. on the grouped column.

SELECT address, COUNT(*)
FROM employee
GROUP BY address;
SELECT SUM(Salary) as TotalSalary FROM employee;

address     COUNT(*)
Ara           2
Delhi            1
Dhandiha             1
Patna            1

example

SELECT Department, MAX(SALARY) as MaxSalary FROM Employee GROUP BY Department;

SELECT Department, Salary, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Department, Salary ORDER BY Department;

SELECT City, Gender, SUM(Salary) as TotalSalary, COUNT(ID) as TotalEmployees FROM Employee GROUP BY CITY, Gender;

SELECT address, COUNT(*) as emp_count FROM employee GROUP BY address

Output :-

address            emp_count
Ara            2
Delhi            1
Dhandiha            1
Patna            1

Using SUM function

SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product;

Using SUM function

SELECT emp_name, SUM(working_hours) AS "Total working hours" FROM employees GROUP BY emp_name;

GROUP BY Clause with MIN function

SELECT emp_name, MIN(working_hours) AS "Minimum working hour" FROM employees GROUP BY emp_name;

COUNT function

SELECT product, COUNT(*) AS "Number of orders" FROM order_details WHERE category = 'produce' GROUP BY product;

MIN function

SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department;

Using MAX function

SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department;

SELECT s.ShipperName, COUNT(ord.OrderID) AS NumberOfOrders FROM Orders as ord LEFT JOIN Shippers as s ON ord.ShipperID = s.ShipperID GROUP BY ShipperName;

SELECT gender FROM members GROUP BY gender;

Restricting query using the HAVING clause

when we will want to restrict our results to a certain given criteria. In such cases , we can use the HAVING clause

SELECT * FROM movies GROUP BY movie_id`, released_year HAVING movie_id = 7

SELECT status, COUNT(*) FROM orders GROUP BY status DESC;

The GROUP BY clause vs. DISTINCT clause

If you use the GROUP BY clause in the SELECT statement without using aggregate functions, the GROUP BY clause behaves like the DISTINCT clause. The following statement uses the GROUP BY clause to select the unique states of customers from the customers table.

SELECT state FROM customers GROUP BY state;

You can achieve a similar result by using the DISTINCT clause:

SELECT DISTINCT state FROM customers;

Note :- The difference between DISTINCT clause and GROUP BY clause is that the GROUP BY clause sorts the result set, whereas the DISTINCT clause does not.

Previous Next

Review & Rating

0.0 / 5

0 Review

(0)

Most Popular Tutorials

What is GROUP BY?

PHP

PHP OOPs

Codeigniter

Laravel

NODE.JS

Javascript

0.0 / 5

0 Review

Write Review Here

Most Popular Tutorials

Important Link