Can group by have two conditions?

Title: Example of using GROUP BY to implement multi-condition grouping

In SQL queries, the GROUP BY statement is usually used to group data by specific columns and Aggregation operations are performed on each group. However, sometimes we need to satisfy two conditions at the same time for grouping, so can we use multiple conditions in the GROUP BY statement? Next, we'll answer this question with concrete code examples.

Suppose we have a data table named "employees", which contains employee information, including name, department and salary. Now we need to group employees by department and gender and calculate the average salary for each group.

First, let’s create a sample data table and insert some data:

CREATE TABLE employees (
  name VARCHAR(50),
  department VARCHAR(50),
  gender VARCHAR(10),
  salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
  ('张三', '销售部', '男', 5000),
  ('李四', '销售部', '男', 5500),
  ('王五', '销售部', '女', 4800),
  ('赵六', '财务部', '女', 6000),
  ('刘七', '财务部', '男', 6500),
  ('陈八', '技术部', '男', 7000),
  ('许九', '技术部', '女', 5500);

Now, we can use the following query statement to group by department and gender, and calculate the average salary:

SELECT department, gender, AVG(salary) AS average_salary
FROM employees
GROUP BY department, gender;

The GROUP BY clause in the above query statement uses two fields: department and gender. It tells the database to group the data by a combination of these two fields. We then use the AVG function to calculate the average salary for each group and name it "average_salary".

After executing the above query statement, we will get the following results:

部门      性别      平均工资
-------------------------
销售部    男       5250.00
销售部    女       4800.00
财务部    女       6000.00
财务部    男       6500.00
技术部    男       7000.00
技术部    女       5500.00

As can be seen from the results, the data is first grouped according to the "department" field, and then within each department according to Group by "Gender" field. In this way, we realize the need to use multiple conditions for grouping.

To summarize, we can use multiple conditions in the GROUP BY statement to group. By using multiple fields in the GROUP BY clause, we can group data according to combinations of these fields and perform aggregation operations on each group. In the above example, we showed how to use GROUP BY to group by department and gender and calculate the average salary.

I hope this article will help you understand how to use GROUP BY for multi-condition grouping in SQL queries. If you have any further questions, please feel free to ask.

The above is the detailed content of Can group by have two conditions?. For more information, please follow other related articles on the PHP Chinese website!