## Question 1: The SUM() function uses a trick

Wrong examples:

``````- Statistics the number of girls in the school table student_sex (student_sex: girl or boy)
SUM(student_sex = "girl") AS"Total number of female students ";The SUM function has an incorrect argument
Copy the code``````

Solution:

`````` SUM(CASE WHEN student_sex = 'girl' THEN 1 ELSE 0 END) AS girls, -- Total number of girls
Copy the code``````

## Problem 2: Calculate the field level of the function or aggregate function, so that the aliases of each other can not be distinguished

Wrong examples:

``````SELECT
COUNT(*) AS total, -- The total number of students in the school
SUM(CASE WHEN student_sex = 'girl' THEN 1 ELSE 0 END) AS girls, -- Count the number of girls
SUM(CASE WHEN student_sex = 'boy' THEN 1 ELSE 0 END) AS boys, -- Count the total number of boys
(sch.boys / stu.total) AS"The proportion of boys in school."FROM
school sch Select * from school table
GROUP BY
student_grade; -- Group by grade
Copy the code``````

If you use this method to query, the following error occurs:

``````Semantic analysis exception - column stu.boys cannot be resolved
Copy the code``````

The SUM() function calculates the total number of boys in the school table and aliases it as boys. Note: this alias is the same as the table alias SCH, so you can’t use SCH. Boys to get the total number of boys in the school!

Solution:

Two levels of SELECT nested queries.

``````SELECT
S.total AS"Total student population ", S. BoysAS"Total number of boys ", S. GirlsAS"Total number of girls ", (S. Boys/ S.total) AS"The proportion of boys in school."FROM
(
SELECT
COUNT(*) AS total,
SUM(CASE WHEN student_sex = 'girl' THEN 1 ELSE 0 END) AS girls, -- Count the number of girls
SUM(CASE WHEN student_sex = 'boy' THEN 1 ELSE 0 END) AS boys, -- Count the total number of boys
FROM
school sch Select * from school table SCH
GROUP BY
student_grade; -- Group by grade
) S;
Copy the code``````

It is ok to inquire normally so!