mysql - SQL查询: Adding Percentage

Question

我有一个有效的查询，但除了我已有的查询之外，我想为每个类别添加一个额外的列，即免费、减少、付费和认证免费，以及与学生总数相比的百分比。谁能帮我吗？

select
        count(case when Lunchstatus = 'P'   then 1 else null end) as Paid
      , count(case when LunchStatus = 'R'   then 1 else null end) as Reduced
      , count(case when LunchStatus = 'F'   then 1 else null end) as Free
      , count(case when LunchStatus = 'fdc' then 1 else null end) as CertifiedFree
      , count(case when LunchStatus = 'P'   then 1 
                   when LunchStatus = 'fdc' then 1 
                   when LunchStatus = 'R'   then 1 
                   when LunchStatus = 'F'   then 1 
                   else null 
                   end) as Total
    from students
    where enroll_status = 0 
      and schoolid = %param1%

Answer 1

将现有查询视为派生表，交叉连接总计数，然后计算百分比:

select
       d.*
     , d.Paid * 100.0 / cj.totcount as paid_pct

     ... more like that

from (
      select
              count(case when Lunchstatus = 'P'   then 1 else null end) as Paid
            , count(case when LunchStatus = 'R'   then 1 else null end) as Reduced
            , count(case when LunchStatus = 'F'   then 1 else null end) as Free
            , count(case when LunchStatus = 'fdc' then 1 else null end) as CertifiedFree
            , count(case when LunchStatus = 'P'   then 1 
                         when LunchStatus = 'fdc' then 1 
                         when LunchStatus = 'R'   then 1 
                         when LunchStatus = 'F'   then 1 
                         else null 
                         end) as Total
      from students
      where enroll_status = 0 
      and schoolid = %param1%
      ) d
CROSS JOIN (
       select count(*) as totcount
       from students
       where enroll_status = 0 
       and schoolid = %param1%
       ) cj