Somewhere on internet i found this question. Thought to share it with you all.
We have two tables, school_students and Class_student.
|Table : School_Students|
Find the total number of student in school and the proportion of the class (In respect of school population).
Result Should be like this
Above implies. Class 3rd hold the 50% of the students.
There are two ways of doing it.
First, A simple nested SELECT.
(SELECT COUNT (1) FROM school_students) as total_school_population,
COUNT (1) / (SELECT CAST(COUNT (1) as decimal(10,3)) FROM school_students) * 100 As [Class_Proportion_In_%]
FROM class_students t
group by t.classname
Second solution is much more optimize way of doing this.
t1.cnt as total_school_population,
(COUNT(1)/ t1.cnt) * 100 As [Class_Proportion_In_%]
SELECT CAST(COUNT(1) as Decimal(10,3)) as cnt
group by t.classname,t1.cnt
On any given day, i will choose second over first.
It is upto reader to identify why second is more optimized of doing the same.
Shoot your comments or queries.