How to Group Data by Multiple Columns in MySQL?

Grouping Data by Multiple Columns in MySQL

When working with large datasets, it is often necessary to group rows by multiple columns to identify trends or patterns. This can be achieved in MySQL using the GROUP BY clause, which allows you to group the results of a query by one or more columns.

To group by multiple columns, simply specify the columns after the GROUP BY keyword, separated by commas. For example:

SELECT tier_id, form_template_id, COUNT(*) AS total
FROM form_data
GROUP BY tier_id, form_template_id;

This query groups the rows in the form_data table by the tier_id and form_template_id columns. The COUNT(*) function counts the number of rows in each group, which can be useful for summarizing data.

Syntax:

The general syntax for GROUP BY multiple columns is:

SELECT column1, column2, ..., columnn
FROM table_name
GROUP BY column1, column2, ..., columnn

Example:

Consider the following employees table:

employee_id	department_id	salary
1	1	50000
2	1	60000
3	2	70000
4	2	80000
5	3	90000

To group the rows by the department_id and salary columns and count the number of employees in each group, you can use the following query:

SELECT department_id, salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id, salary;

This query will return the following result:

department_id	salary	employee_count
1	50000	2
1	60000	1
2	70000	1
2	80000	1
3	90000	1

The above is the detailed content of How to Group Data by Multiple Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!