CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
Sigal Cohen
Sigal Cohen

Reputation: 21

Improving query for more efficient writing

Diagram

How would you improve the following query in SQL Server to find in which of the courses the men/women ratio is over 70%? (I added a diagram of all tables related..not all of them are necessary)

SELECT 
    F.CourseId, 
    CAST(ROUND(CAST(M.Men AS FLOAT) / CAST(F.Women AS FLOAT) * 100, 2) AS NUMERIC(12, 2)) AS Ratio
FROM 
    (SELECT 
         c.CourseId, COUNT(DISTINCT c.StudentId) AS Women
     FROM 
         Classrooms c
     INNER JOIN 
         Students s ON c.StudentId = s.StudentId
     WHERE 
         s.Gender = 'F'
     GROUP BY 
         c.CourseId) F
INNER JOIN 
    (SELECT 
         c.CourseId, COUNT(DISTINCT c.StudentId) AS Men
     FROM 
         Classrooms c
     INNER JOIN 
         Students s ON c.StudentId = s.StudentId
     WHERE  
         s.Gender = 'M'
     GROUP BY 
         c.CourseId) M ON F.CourseId = M.CourseId
WHERE 
    CAST(M.Men AS FLOAT) / CAST(F.Women AS FLOAT) * 100 > 70
ORDER BY 
    F.CourseId

I want to practice effective and shorter (better) queries.

Thank you all!

Table Examples

Upvotes: 0

Views: 48

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can use single query like:

SELECT 
     c.CourseId, 
     COUNT(DISTINCT CASE WHEN s.Gender = 'F' THEN c.StudentId ELSE NULL END) AS Women,
     COUNT(DISTINCT CASE WHEN s.Gender = 'M' THEN c.StudentId ELSE NULL END) AS Man,
     CAST(COUNT(DISTINCT CASE WHEN s.Gender = 'M' THEN c.StudentId ELSE NULL END) AS FLOAT) / COUNT(DISTINCT c.StudentId) AS MensRate
FROM Classrooms c
INNER JOIN Students s ON c.StudentId = s.StudentId
GROUP BY c.CourseId
HAVING CAST(COUNT(DISTINCT CASE WHEN s.Gender = 'M' THEN c.StudentId ELSE NULL END) AS FLOAT) / COUNT(DISTINCT c.StudentId) > 0.7

MS SQL fiddle group by

A mean you should to divide Men's count to total count for rate calculate.

Upvotes: 1

Related Questions