MySQL求和计算错误

Question

我正在努力计算 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');

我有一个查询，它根据属于特定步骤的每个计划的状态来计算每个公司每个步骤的完成率。

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;

这个似乎根本不起作用，因为我收到的值与预期完全不同。

你能解释一下我错了什么吗？

亲切的问候!

Answer 1

您似乎正在追求以下内容...

SELECT company
     , AVG(`ratio %`) n
  FROM
     (
        -- your query here --
     ) x
 GROUP
    BY company;