title: SQL: Two select statements in one query ( Same Table )
body: <p>InfoTable</p>
  <pre><code>   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
  </code></pre>
  <p>what i am trying to do is multiple select to count the average of 'School A' and other school</p>
  <p>i can find what i wanted by using 2 different sql statement:</p>
  <pre><code>SELECT school,AVG(score) as average FROM infoTable where school = 'school A';
  </code></pre>
  <p>and</p>
  <pre><code>SELECT school,AVG(score) as average FROM infoTable where school != 'school A';
  </code></pre>
  <p>but is that possible to do it in 1 statement ? where it will become :</p>
  <pre><code>  school | average | other_school_average
  school A |  60     | 70
  </code></pre>
  <p>=============================================================
  (Update): Above issue has been solved.</p>
  <pre><code>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;
  </code></pre>
  <p>Above code is provided by @Tim Biegeleisen , the code work as intended , and the result is what i wanted.</p>
  <p>Below is the latest question:</p>
  <p>What i wanted to archieve is :</p>
  <p>Count the total number of <code>School A</code> that score is higher than the <code>other_school_average</code> .</p>
  <hr />
  <p>Example:</p>
  <p><code>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</code></p>
  <p>What i have tried , but show error :</p>
  <pre><code>IFNULL(COUNT(CASE WHEN (school = 'school A' and score &gt;= AVG(CASE WHEN school != 'school A' THEN score END)) THEN score END),0) AS school_count,
  </code></pre>