none
Hot to use nested aggregate function RRS feed

  • Question

  • Hi All,

    I use this code to calculate result statistics in a school system ,but this code for term "A or B" but when i want to use this code to calculate statistics for two terms i can not use 

    case when SUM(Scores) then ...

    so my my question is whats the addition code in order to calculate terms

    here is my Selection code

        select 
             COUNT(*) as "TotalOfState" ,
             SUM(Case when Scores >=0 then 1 else 0 end) as " Attend" ,
             SUM(Case when Scores < 0 then 1 else 0 end) as " Abcent" ,
             SUM(Case when Scores >=0 and Scores >= Pass_mark then 1 else 0 end) as " Successed", 
             SUM(Case when Scores >=0 and Scores <  Pass_mark then 1 else 0 end) as " Failured",
             AVG(Case when Scores >= 0 then Scores else 0 end) as "Average",
            -- CAST(SUM(Case when Scores >= 0 then 1 else 0 end)as float)/SUM(Case when Scores >= 0 then 1 else 0 end) *100 as " Total successed percent",
             CAST(SUM(Case when Scores >=0 and Scores >= Pass_mark then 1 else 0 end)as float)/SUM(Case when Scores >=0 then 1 else 0.00001 end) *100 as " Total successed percent",
         Class_label,St_level,Level_name,ClassLabel_name,Term_name,Subject,Subject_name,Year_name  
         from Result_set
         where  Year_name_ID	          = @Year
               and St_level			  = @Level
               and Term_time		  = @Term
    --           and Subject		  = @In_Subject_ID
               and Exam_time		  = @Exam_time
               and Exam_type       	          = @Exam_type
    Group by Class_label,St_level,Level_name ,ClassLabel_name,
    Term_name,Subject,Subject_name,Year_name
    order by Class_label,subject
    


    Esmat

    Saturday, July 6, 2019 8:37 AM

Answers

All replies