user3258571 user3258571 - 6 months ago 10
SQL Question

Need to Add the Count in Select

The following query allows me to count the Type of class a student takes.

SELECT student_schedule.Grade, LEFT(student_schedule.Course,2) AS 'Type',
COUNT(*) AS 'Count'
FROM student_schedule
GROUP BY Type, Grade
ORDER BY Grade, Type


The result is:

enter image description here

I would now like to find the ratio for each Type by Grade. The fourth column after Count would be (303+529+50 or 882) for all rows of Grade 7 and (719+139+15 or 873) for Grade 8. I need a SUM in my SELECT statement I think. To clarify, the fourth column would be 882 for Grade 7 and 873 for Grade 8 in every row for that grade. Any help would be appreciated.

Answer

Try this:

SELECT 
   o.Grade as 'Grade',
   LEFT(student_schedule.Course,2) AS 'Type',  
   COUNT(*) AS 'Count',
   COUNT(*) / (SELECT COUNT(*) FROM student_schedule as i WHERE i.Grade = o.Grade) as 'ratio'
FROM student_schedule as o
GROUP BY Type, Grade
ORDER BY Grade, Type
Comments