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!