我正在努力计算mysql workbench中另一个计算的总和。我真的不知道如何用语言来解释，所以我会提供一些数据。
这里有表格和数据：

drop database if exists GDPR;
create database if not exists GDPR;

use GDPR;

drop table if exists Company;

create table Company 
(
    id_company int not null auto_increment,
    name varchar (50),
    primary key (id_company)
) auto_increment = 1;

drop table if exists GDPR_steps;

create table GDPR_steps 
(
    id_step int not null auto_increment,
    id_company int,
    name varchar (50),
    primary key (id_step),
    foreign key (id_company) references Company (id_company)
) auto_increment = 1;

drop table if exists compliance;

create table compliance  
(
    id_com int not null auto_increment,
    id_step int,
    initiative varchar (50),
    status varchar (10),
    primary key (id_com),
    foreign key (id_step) references gdpr_steps (id_step)
) auto_increment = 1;

insert into company 
values (null, 'Mango'), (null, 'Kiwi');

insert into gdpr_steps 
values (null, 1, 'Awareness'), (null, 1, 'Information you hold'),
       (null, 2, 'Awareness'), (null, 2, 'Information you hold');

insert into compliance 
values (null, 1, 'E-mail all employees',  '1'),
       (null, 1, 'E-mail all applicants', '0'),
       (null, 2, 'Delete some data', '1'),
       (null, 3, 'Call stakeholders', '1'),
       (null, 4, 'Review data', '0');

我有这个查询，它计算每个公司基于 status 每个 initiatives 属于某一步的。

select 
    company.name as 'Company',
    gdpr_steps.name as 'ID Step',
    (sum(compliance.status)/count(compliance.status)) * 100 as 'Ratio %'
from
    compliance, company, gdpr_steps
where 
    gdpr_steps.id_step = compliance.id_step 
    and company.id_company = gdpr_steps.id_company
group by 
    compliance.id_step, company.id_company;

上面的查询返回以下输出：

Company   ID Step               Ratio %
-----------------------------------------
Mango     Awareness             50
Mango     Information you hold  100
Kiwi      Awareness             100
Kiwi      Information you hold  0

现在，当我想计算每家公司的比率时（例如，将步骤1中的比率与步骤2中的比率相加，然后除以2），我无法让它工作。就像

Company   Overall ratio %
Mango     (Awareness (50) + Information you hold (100)) / nr of steps (2 in our case)
Kiwi      (Awareness  (0) + Information you hold (100)) / nr of steps (2 in our case)

在我们的案例中，结果会是：

Name    Overall ratio %
Mango   75
Kiwi    50

我试过类似的方法

select 
    company.name,
    ((sum(compliance.status)/count(compliance.status)) * 100)/ count(gdpr_steps.id_step)
from
    compliance, company, gdpr_steps 
where 
    gdpr_steps.id_step = compliance.id_step 
    and company.id_company = gdpr_steps.id_company
group by 
    company.id_company;

这个似乎根本不起作用，因为我收到的值与预期值完全不同。
你能解释一下我做错了什么吗？
谨致问候！