PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
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:
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.
Table
File Name :
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;
File Name :
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.
File name : index.php
SELECT address, COUNT(*)
FROM employee
GROUP BY address;
SELECT SUM(Salary) as TotalSalary FROM employee;
Output :-
address COUNT(*)
Ara 2
Delhi 1
Dhandiha 1
Patna 1
example
SELECT Department, MAX(SALARY) as MaxSalary
FROM Employee
GROUP BY Department;
example 2
SELECT Department, Salary, COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY Department, Salary
ORDER BY Department;
example 3:-
SELECT City, Gender,
SUM(Salary) as TotalSalary,
COUNT(ID) as TotalEmployees
FROM Employee
GROUP BY CITY, Gender;
File Name :
SELECT address, COUNT(*) as emp_count FROM employee GROUP BY address
Output :-
File Name :
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;
File Name :
SELECT gender FROM members ;
Output
File Name :
gender
Female
Female
Male
Female
Male
Male
Male
Male
Male
File Name :
SELECT gender FROM members GROUP BY gender;
Output :-
File Name :
gender
Female
Male
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
File Name :
SELECT * FROM movies GROUP BY movie_id`, released_year HAVING movie_id = 7
File Name :
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.
File Name :
SELECT state FROM customers GROUP BY state;
You can achieve a similar result by using the DISTINCT clause:
File Name :
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.
File Name :
File Name :
File Name :