MySQL Group by With Count Function

  2. HowTo
  3. MySQL Howtos
  4. MySQL Group by With Count Function
Abdul Basit Feb 15, 2024
MySQL MySQL Group By
  1. GROUP BY Syntax in MySQL
  2. GROUP BY and Aggregate Functions in MySQL
  3. GROUP BY and HAVING Clause in MySQL
  4. GROUP BY Count() on Multiple Columns in MySQL
MySQL Group by With Count Function

This tutorial will discuss the SQL GROUP BY clause in conjunction with the aggregate functions using MySQL.

The GROUP BY clause is often used along with some aggregate functions like COUNT(), SUM(), MIN(), MAX(), and AVG().

GROUP BY Syntax in MySQL

In MySQL, the following syntax is used for grouping the resultant view by some column name(s).

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s);

GROUP BY and Aggregate Functions in MySQL

In SQL, the GROUP BY clause can be used with the aggregate function to count, sum, or aggregate the number of records against certain conditions.

Example 1: Assume that we have an ORDERS table and want to count the number of orders grouped by orders’ date.

Orders Table

We can achieve this by using the following query.

SELECT COUNT(OrderID) as Count, OrderDate
FROM Orders
GROUP BY OrderDate;

The above query will retrieve all the orders by grouping them by their OrderDate and displays a single record per OrderDate. Therefore, each record in the resultant view will contain OrderDate and the count of all the OrderIDs on this date.

The output of the query shall be as:

Orders by Date

Example 2: Below is another example to find each employee’s total salary. Assume we are using the following table for retrieving the results.

Employee Table

SELECT e.NAME, SUM(e.SALARY)
FROM Employee e
GROUP BY e.NAME;

In the above query, we first select all the NAMEs from the table and then apply the SUM function to calculate the sum of salaries against each name. Therefore, there should only be a single record corresponding to each Name.

The query’s output should be as:

Output Total Salary Group by Names

GROUP BY and HAVING Clause in MySQL

The HAVING clause can be used in conjunction with the GROUP BY clause to further filter the results based on specific conditions, as demonstrated by the following query.

SELECT e.NAME, SUM(e.SALARY)
FROM Employee e
GROUP BY e.NAME
HAVING SUM(SALARY)>44000;

This query is the same as above; however, the only difference is that we have applied the HAVING clause to filter the salary further to include only those records with the final salary sum greater than 44000.

The result of this query looks like this:

Output Names Having Salary Greater than 44000

GROUP BY Count() on Multiple Columns in MySQL

The COUNT() aggregate function can be used along with the GROUP BY clause having multiple columns. In this case, the results are first grouped by the first column, then by the next, and so on for any further columns.

After that, all the rows with the same grouped values are counted and displayed as one.

Assume that we want to count a particular customer’s orders on a specific date from the following table.

Orders Table

We can do that by using the following query.

Select CustomerId, OrderDate, COUNT(OrderId) as OrdersByCustomerByDate
FROM ORDERS
GROUP BY CustomerId, OrderDate;

All the orders are first grouped by CustomerId and then by OrderDate. After that, all those records where CustomerIDs and OrderDates are the same are counted and displayed as a single record, as depicted in the query result below.

Output Order by Customer by Date

The customer with $CustomerId = 1$ has three orders: one on 30-Jul-2022 and two on the date 31-Jul-2022.

All the two orders of this customer for 31-Jul-2022 are aggregated on the second row of the query result with $OrdersByCustomerByDate = 2$. The same applies to the rest of the records.