MySQL query by age group

The following is the core SQL code of the project based on the age group of men and women:

COUNT (tr. id) AS 'Total number of health checks', SUM (case when s. sex = 1 THEN 1 ELSE 0 END) AS 'total male check', SUM (case when s. sex = 0 THEN 1 ELSE 0 END) AS 'total female check', SUM (case when s. sex = 1 AND tr. age> = 18 AND tr. age <= 29 THEN 1 ELSE 0 END) AS 'male 18--29 year', SUM (case when s. sex = 0 AND tr. age> = 18 AND tr. age <= 29 THEN 1 ELSE 0 END) AS 'female 18--29 year', SUM (case when s. sex = 1 AND tr. age <= 45 AND tr. age> = 30 THEN 1 ELSE 0 END) AS 'male 30--39 year', SUM (case when s. sex = 0 AND tr. age <= 45 AND tr. age> = 30 THEN 1 ELSE 0 END) AS 'female 30--39 year', SUM (case when s. sex = 1 AND tr. age <= 50 AND tr. age> = 46 THEN 1 ELSE 0 END) AS 'male 40--49 year', SUM (case when s. sex = 0 AND tr. age <= 50 AND tr. age> = 46 THEN 1 ELSE 0 END) AS 'female 40--49 year', SUM (case when s. sex = 1 AND tr. age <= 60 AND tr. age> 50 THEN 1 ELSE 0 END) AS 'male 50--59 year', SUM (case when s. sex = 0 AND tr. age <= 60 AND tr. age> 50 THEN 1 ELSE 0 END) AS 'female 50--59 year', SUM (case when s. sex = 1 AND tr. age <= 70 AND tr. age> 60 THEN 1 ELSE 0 END) AS 'male 60--69 year', SUM (case when s. sex = 0 AND tr. age <= 70 AND tr. age> 60 THEN 1 ELSE 0 END) AS 'female 60--69 year', SUM (case when s. sex = 1 AND tr. age <= 80 AND tr. age> 70 THEN 1 ELSE 0 END) AS 'male 70--79 year', SUM (case when s. sex = 0 AND tr. age <= 80 AND tr. age> 70 THEN 1 ELSE 0 END) AS 'female 70--79 year', SUM (case when s. sex = 1 AND tr. age> 80 THEN 1 ELSE 0 END) AS 'male 80 + ', SUM (case when s. sex = 0 AND tr. age> 80 THEN 1 ELSE 0 END) AS 'female over 80 year'

Let me explain one sentence.

SUM (case when s. sex = 1 AND tr. age> = 18 AND tr. age <= 29 THEN 1 ELSE 0 END) AS 'male 18--29 year ',

First look at the section in the brackets of sum.

Case when sex = 1 and age> = 18 and age <= 29 then 1 else 0 end

It indicates that if the gender is 1 (that is, male) and the age is between 18 and 29 years old, the gender is 1.

Case and end are keywords that can be understood as the beginning and end of a statement.

When is equivalent to if, and then is the result displayed after judgment. If the value is 1, the value is 0 if the value is not 1.

Sum is to add each value. For example, 1 + 1 + 0 + 1 + 0 + 1 + 1 + 0 + 1 + 1

The final statistical result of the project is as follows:

Zookeeper