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 |
| StudentID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
Class_Student.
| StudentID | ClassName |
| 1 | First |
| 2 | First |
| 3 | Second |
| 4 | Second |
| 5 | Second |
| 6 | Third |
| 7 | Third |
| 8 | Third |
| 9 | Third |
| 10 | Third |
Find the total number of student in school and the proportion of the class (In respect of school population).
Result Should be like this
| classname | total_school_population | Class_Proportion_In_% |
| First | 10 | 20 |
| Second | 10 | 30 |
| Third | 10 | 50 |
Above implies. Class 3rd hold the 50% of the students.
Solution :
There are two ways of doing it.
First, A simple nested SELECT.
SELECT
t.classname,
(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.
SELECT
t.classname,
t1.cnt as total_school_population,
(COUNT(1)/ t1.cnt) * 100 As [Class_Proportion_In_%]
FROM
class_students t
CROSS JOIN
(
SELECT CAST(COUNT(1) as Decimal(10,3)) as cnt
from school_students
) t1
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.
Thank You.