SQL: Two select statements in one query ( Same Table )-CodePudding

InfoTable

   id | school  | grade | score
    1 | school A| A     | 100
    2 | school B| D     | 25
    3 | school B| C     | 50
    4 | school A| B     | 67
    5 | school C| B     | 70
    6 | school A| D     | 10
    6 | school B| A     | 85

what i am trying to do is multiple select to count the average of 'School A' and other school

i can find what i wanted by using 2 different sql statement:

SELECT school,AVG(score) as average FROM infoTable where school = 'school A';

and

SELECT school,AVG(score) as average FROM infoTable where school != 'school A';

but is that possible to do it in 1 statement ? where it will become :

  school | average | other_school_average
school A |  60     | 70

============================================================= (Update): Above issue has been solved.

SELECT
    AVG(CASE WHEN school = 'school A' THEN score END) AS school_a_average,
    AVG(CASE WHEN school != 'school A' THEN score END) AS other_school_average,
    SUM(school = 'school A') - SUM(school != 'school A') AS diff
FROM infoTable;

Above code is provided by @Tim Biegeleisen , the code work as intended , and the result is what i wanted.

Below is the latest question:

What i wanted to archieve is :

Count the total number of School A that score is higher than the other_school_average .

Example:

average of other school score is (38.75%) based on the table provided , so it able to calculate the total count of school A is 3

What i have tried , but show error :

IFNULL(COUNT(CASE WHEN (school = 'school A' and score >= AVG(CASE WHEN school != 'school A' THEN score END)) THEN score END),0) AS school_count,

CodePudding user response：

Use conditional aggregation:

SELECT
    AVG(CASE WHEN school = 'school A' THEN score END) AS school_a_average,
    AVG(CASE WHEN school != 'school A' THEN score END) AS other_school_average,
    SUM(school = 'school A') - SUM(school != 'school A') AS diff
FROM infoTable;

CodePudding user response：

Please try not to keep updating your requirement relentlessly, which may upset the person working on your answer. You final(hopefully) requirement only needs to be modified a bit after the answer Tim gave you. Here is the answer based on Tim's, which you probably already figured out. Please consider opening another thread should you decide to press another update.

SELECT
    AVG(CASE WHEN school = 'school A' THEN score END) AS school_a_average,
    AVG(CASE WHEN school != 'school A' THEN score END) AS other_school_average,
    SUM(school = 'school A') - SUM(school != 'school A') AS diff,
    sum(if(
        CASE WHEN school = 'school A' THEN score END - 
        (select AVG(CASE WHEN school != 'school A' THEN score END) from InfoTable) 
        >0 ,1,0)
        ) as higher_than_average
FROM InfoTable;

NOTE:The average of other school score is actually 57.5% based on the table provided, NOT 38.75% as you claimed , so the total count of school A which is higher than that is 2, NOT 3.