我有一个问题，在这个条件计数和被困在这里一天！希望能从你们这里找到解决办法。所以我有一个等级表，它有三列：1。大科目2。主题（这是大主题的细分）3。等级

Grand_subject      Subject          Grade
-------------------------------------------------------
    Discrete Math   Discrete Math 1      A
                                         B
                                         B
                                         A
                                         A
                                         B
------------------------------------------------------
    Statistics       Statistics 1        A
                                         B
                                         B
                                         A
                                         A
-------------------------------------------------------
    Physics         Physics Basic        A
                                         A
                                         A
                                         B
                     ----------------------------------
                      Physics 1          B
                                         B
                                         B
                                         A
                     ----------------------------------
                      Physics 2          A
                                         A
                                         B
-------------------------------------------------------
    History           History 1          B
                                         A
                                         A
                                         B
                     ----------------------------------
                      History 2          A
                                         A
                                         B
                                         B

所以，我要做的是，我要计算大科目和每门科目的成绩百分比{a成绩百分比=多少a除以多少a+b成绩（多少a/（多少a+多少b）*100%}。有以下两条规则：1）。对于离散数学和统计学这两大学科，成绩计算应结合数学；然而，对于其他大科目将单独计算（物理，历史）2）。对于科目栏，应为每个科目单独计算成绩。
预期输出如下：

A_Grade_Percentage
-----------------------------------------------------------------
    Maths (Discrete Math + Statistics)         54.50%
    Discrete Math 1                             50%
    Statistics 1                                60%
-----------------------------------------------------------------
    Physics                                   54.50%
    Physics Basic                               75%
    Physics 1                                   25%
    Physics 2                                 66.70%
-----------------------------------------------------------------
    History                                     50%
    History 1                                   50%
    History 2                                   50%
-----------------------------------------------------------------
    Geography                                   25%

到目前为止，我已经尝试了这么长的代码：

with on_grand_subject as(
select
case when grand_subject in ('Discrete Math', 'Statistics') then 'Maths'
     else grand_subject end as grand_subject,
count(case when grade = 'A' then 1 else 0 end) as A_Grade,
100.0*count(case when grade = 'A' then 1 else 0 end) / 
   (count(case when grade = 'A' then 1 else 0 end) + count(case when grade = 'B' then 1 else 0 end)) 
   as A_Grade_percentage,
from
grade_table
where
 ...................
group by grand_subject in ('Discrete Math', 'Statistics') then 'Maths' else grand_subject end
order by grand_subject
limit 1000
),

on_subject as(
select
grand_subject,
subject,
count(case when grade = 'A' then 1 else 0 end) as A_Grade,
100.0*count(case when grade = 'A' then 1 else 0 end) / 
   (count(case when grade = 'A' then 1 else 0 end) + count(case when grade = 'B' then 1 else 0 end)) 
   as A_Grade_percentage,
from
grade_table
where
 ...................
group by grand_subject, subject
order by subject
limit 1000
)

select
g.grand_subject, s.subject, g.A_Grade, g.A_Grade_percentage, s.A_Grade, g.A_Grade_percentage
from on_grand_subject g join on_subject s on g.grand_subject = s.grand_subject

但是输出看起来很奇怪，并不是它应该的样子。有什么解决办法吗？非常感谢！