Count Male and Female who scored greater than average marks

This question was asked in a practice Test, and I am new to SQL.

Question was to determine the average marks scored by the male and female students. Also, you are required to determine the count of the students whose score is greater than their gender’s average.

Table Description:

=======================
| Field    |  Type    |  
=======================
| RollNo   |  int     |
| Name     |  text    |
| Gender   |  text    |
| Score    |  text    |
=======================

Example Input:

=========================================
| RollNo   |  Name   | Gender |  Score  |
=========================================
| 101      |  John   |   M    |  85     |
| 102      |  Tracy  |   F    |  79     |
| 103      |  Jake   |   M    |  92     |
| 104      |  Edgar  |   M    |         |
| 105      |  Monica |   F    |  25     |
| 106      |  Alicia |   F    |  50     |
| 107      |  Yash   |   M    |  68     |
=========================================

Output:

=================================
| Gender   |  Count |  AvgScore |
=================================
| Female   |  1     |   51      |
| Male     |  2     |   81      |
=================================

Output Explantion:
There are 4 male student but 1 student didn’t attempt so we ignore that null value.
So avg score of male is (85 + 92 + 68)/3 = 81.67(take only integer value)
and only 2 student has score above 81

This I tried just for counting number of male and female:

SELECT
CASE WHEN S.Gender="M" then "Male" else "Female" END AS Gender
COUNT(Gender) as Count
FROM Students AS S
GROUP BY Gender

I don’t know where to put AVG function. Should I use nested queries? or is there anything that I pre-query the avg then compare with every rows?

Answer

You need to do two computations. The first is determining the averages you need. The second is retrieving the rows with those averages available. The trick you need is to use a subquery for the first computation, and JOIN it for the second computation.

Here’s the first computation formulated as a subquery.

               SELECT Gender, AVG(Score) AvgScore
                 FROM scores
                GROUP BY Gender

That needs to be joined to your details like this.

   SELECT avg.Gender, avg.AvgScore,
          SUM(scores.Score > avg.AvgScore) AboveAverageCount
     FROM (
               SELECT Gender, AVG(Score) AvgScore
                 FROM scores
                GROUP BY Gender
          ) avg
     JOIN scores ON avg.Gender = scores.Gender
    GROUP BY Gender

See this. https://www.db-fiddle.com/f/jTWnYkAzRfMVkdkM3ve68R/0

The AVG() function omits NULL values automatically.

The SUM() I suggest works because comparison expressions like score > AvgScore have a value of either 1 for true, or 0 for false. That’s a MySQL quirk, and doesn’t work in other SQL dialects.