MySQL 8: Count rows in each group with GROUP BY and COUNT

Introduction

In MySQL, the COUNT() function is one of the most common aggregate functions used in conjunction with the GROUP BY clause to summarize or aggregate data stored in a database. This tutorial will guide you through using both COUNT() and GROUP BY in MySQL 8 to count the number of rows in different groups of data.

Prerequisites:

• MySQL Server 8 installed and running.
• Basic knowledge of SQL commands.
• Access to a database and permission to create tables and insert data.

Understanding GROUP BY and COUNT()

The GROUP BY clause is used to arrange identical data into groups. The COUNT() function is then used to count the number of rows in each group. This can be particularly useful for summarizing information, such as counting the number of customers in each country or the number of products in each category.

Basic Example of GROUP BY with COUNT()

Consider a table employees that includes the following columns:

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50)
);

We’ll insert some sample data into the table:

INSERT INTO employees (name, department) VALUES
('Jane Doe', 'Sales'),
('John Doe', 'Marketing'),
('Alice Smith', 'Sales'),
('Bob Johnson', 'Marketing'),
('Charlie Brown', 'Sales');

To count the number of employees in each department, you would use:

SELECT department, COUNT(*) as department_count
FROM employees
GROUP BY department;

The output would be:

+------------+-----------------+
| department | department_count |
+------------+-----------------+
| Sales      |               3 |
| Marketing  |               2 |
+------------+-----------------+

Working with Conditions

You can include conditions using the WHERE clause before the GROUP BY clause:

SELECT department, COUNT(*) as department_count
FROM employees
WHERE name LIKE 'J%'
GROUP BY department;

If you only want to include employees whose names start with ‘J’, the result would be:

+------------+-----------------+
| department | department_count |
+------------+-----------------+
| Sales      |               1 |
| Marketing  |               1 |
+------------+-----------------+

GROUP BY with Multiple Columns

You can also group by multiple columns. Suppose you want to count employees by department and their status:

ALTER TABLE employees ADD COLUMN status VARCHAR(10);

UPDATE employees SET status = 'Active' WHERE id IN (1, 2, 4);
UPDATE employees SET status = 'Inactive' WHERE id IN (3, 5);

SELECT department, status, COUNT(*) as department_status_count
FROM employees
GROUP BY department, status;

The output would then be:

+------------+----------+-------------------------+
| department | status   | department_status_count |
+------------+----------+-------------------------+
| Marketing  | Active   |                       2 |
| Sales      | Active   |                       1 |
| Sales      | Inactive |                       2 |
+------------+----------+-------------------------+

Advanced GROUP BY Queries

For more complex data sets, you might have to join multiple tables to properly group and count the data. Consider you have an orders table with a foreign key to the customers table:

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

Assume both tables have appropriate sample data.

Here’s how to count orders by customer:

SELECT c.name, COUNT(*) as order_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY c.name;

And the result might look like:

+------------+-------------+
| name       | order_count |
+------------+-------------+
| John Doe   |           3 |
| Jane Smith |           5 |
+------------+-------------+

Using HAVING with GROUP BY

The HAVING clause can be used to apply a condition to groups after they have been created:

SELECT department, COUNT(*) as department_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

This will only return departments with more than one employee:

+------------+-----------------+
| department | department_count |
+------------+-----------------+
| Sales      |               3 |
| Marketing  |               2 |
+------------+-----------------+

Note that HAVING is used instead of WHERE because it filters groups instead of individual rows.

Conclusion

In conclusion, the GROUP BY clause paired with the COUNT() function in MySQL 8 is a powerful tool for organizing and counting data across grouped categories. By practicing the examples provided, you’ll deepen your understanding of how to generate informative summaries from your database.