Count Male and Female who scored greater than average marks

user_10566155 0
Viewed 1412

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?

mysql sql
4 Answers

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.

user_2056080

The AVG should be included in the select statement, together with gender. For example

SELECT 
  S.Gender,
  a.avg_score,
  SUM(S.score > a.avg_score) as counts
FROM scores S
LEFT JOIN 
(
  SELECT
     Gender,
     AVG(S.Score) AS avg_score
  FROM scores AS S
  GROUP BY S.Gender) a
USING(Gender)
WHERE S.Score IS NOT NULL
GROUP BY 1,2
user_59395910

You can use analytical function avg as follows:

Select gender,
       Count(case when score > avg_score_gender then 1 end) as cnt,
       avg_score_gender as avg_score
(Select t.*,
       Avg(score) over (partition by gender) as avg_score_gender
  From t) t
Group by gender, avg_score_gender
user_115656290 
   SELECT CASE WHEN scores.Gender="M" then "Male" else "Female" END AS Gender,
          SUM(scores.Score > avg.AvgScore) Count, floor(avg.AvgScore) as avgScore
     FROM (
               SELECT Gender, AVG(Score) AvgScore
                 FROM scores
                GROUP BY Gender
          ) avg
     JOIN scores ON avg.Gender = scores.Gender
    GROUP BY scores.Gender order by avg.AvgScore ASC
user_112016710
Related