This post was asked by the blogger’s college roommate in a group. The question is as follows:

How many people are older than 10 and younger than 10 in different classes and grades in a school?

The number of people over 10 years old/the number of people under 10 years old in Grade 1 / Class 1 / The number of people over 10 years old/the number of people under 10 years old

Below I first build a basic information table, convenient for readers to understand this problem, build table SQL as follows:

create database sql_client;
use sql_client;
create table student(
    id int AUTO_INCREMENT primary key,
    name varchar(255),
    age int,
    grade int,
    class int
);

insert into student(name, age, grade, class) values ('a'.10.1.1), ('b'.11.1.1), ('c'.12.2.1), ('d'.20.2.1), ('e'.21.3.1), ('f'.22.2.1), ('g'.23.1.1), ('h'.30.3.1);
Copy the code

Create a table with id, name, age, grade, and class fields as follows:There are 3 categories of students’ ages: 10-20, 20-30, 30-… , so we first mark according to the age category:

select age,grade,class,
case when age> =10 and age <20 then 'low' 
when age > =20 and age <30 then 'mid' 
else 'high' end 
category
from student
Copy the code

The data after marking is as follows:According to the business requirements proposed at the beginning, we want to display the three categories of low, middle and high age as columns. Above, we marked rows, so we need a row conversion operation next

select grade,class,
ifnull(sum(if(category='low'.1.0)),0)low,
ifnull(sum(if(category='mid'.1.0)),0)mid,
ifnull(sum(if(category='high'.1.0)),0)high 
from(
select age,grade,class,case when age> =10 and age <20 then 'low' when age > =20 and age <30 then 'mid' else 'high' end category
from student)t group by grade,class;
Copy the code

After executing the above SQL, you can get the answer to the question: