mysql - 使用 MySQL 和 Group By 进行划分

Question

我正在查看之前关于“使用计数除法”的帖子，似乎在按部分除法时遇到了问题。下面是我的查询，我得到了结果，但结果是错误的，因为 count(*) 的“除以”似乎使用的是所有商店的计数，而不是按商店分组。

我感兴趣的是“除以”还应该基于该特定商店的计数。我的除法查询的第二部分应该类似于“select count(*) *100 group by stores”

SELECT store, CAST(((  
    SUM(CASE WHEN SCORE BETWEEN 9 AND 10 THEN 1 ELSE 0 END) * 1.0 -
    SUM(CASE WHEN SCORE BETWEEN 0 AND 6 THEN 1 ELSE 0 END)) / COUNT(*) * 100) AS signed) AS NPS
FROM npsnetsuite
WHERE SCORE IS NOT NULL group by STORE;

例子:

Store A has got total count of 7 rows
Store B has got total count of 12 rows

划分应该是

XX/7 for Store A
XX/12 for Store B

示例数据:

Date       Store    Score   Case When between 9-10  case when 0-6
18/04/2016  A         9                  1  
20/04/2016  A         8     
26/04/2016  A         10                 1  
29/04/2016  A         8     
30/04/2016  A        10                  1  
01/05/2016  A        10                  1  
06/05/2016  A        8      
11/05/2016  A        10                  1  
23/05/2016  A        10                  1  
04/06/2016  A         6                               1
09/06/2016  A         8     
08/06/2016  A        10                  1  
05/04/2016  B         4                               1 
07/04/2016  B        10                  1  
18/04/2016  B        10                  1  
27/04/2016  B         8     
01/05/2016  B         9                  1  
13/05/2016  B        10                  1  
13/06/2016  B         1                              1
14/06/2016  B         6                              1

示例数据:

期望的输出:

Store A 50
Store B 13

所以基本上商店 A 是 (7-1)/12 * 100 = 50。

7 是分数 9 到 10 时的计数，1 是分数 0 时的计数到 6。因此，我正确理解的第一部分是每家商店的分数 9 到 10 减去分数 0 到 6 的数量。我需要除以该特定商店的数量。如上例，Store A 包含 12 行，Store B 包含 8 行。我当前的查询是计算所有商店的数量。

谢谢

Answer 1

谢谢大家~终于通过这个查询得到了上面输出的结果:):

select a.STORE, round((a.count_one / b.count_two) * 100) as final_count from 
(select SCORE, STORE, SUM(CASE WHEN SCORE BETWEEN 9 AND 10 THEN 1 ELSE 0 END) * 1.0 -
  SUM(CASE WHEN SCORE BETWEEN 0 AND 6 THEN 1 ELSE 0 END) as count_one from npsnetsuite where SCORE IS NOT NULL group by STORE) a,
(select SCORE, STORE, count(*) as count_two from npsnetsuite where  SCORE IS NOT NULL group by STORE) b
where a.STORE = b.STORE and a.SCORE IS NOT NULL;