mysql - SQL - 百分比

Question

我需要进行查询，结果是百分比。

我现在的查询如下所示:

select COUNT(CREATE_WEEKDAY),
       CREATE_WEEKDAY,
       COUNT(CREATE_WEEKDAY) * 100 /
from call_view
WHERE (create_month = MONTH(NOW() - INTERVAL 1 MONTH))
AND (create_year = YEAR(NOW() - INTERVAL 1 MONTH))
AND CUSTOMER_COMPANY_NAME = "Company"
group by CREATE_WEEKDAY

如果我开始查询，结果显示:

COUNT(CREATE_WEEKDAY) |  CREATE_WEEKDAY  |      COUNT(CREATE_WEEKDAY) * 100
111                        Friday              11100
225                        MONDAY              22500

等等......

我希望最后一列显示

"COUNT(CREATE_WEEKDAY) * 100 / SUM(COUNT(CREATE_WEEKDAY))"

->>最后一部分应该是所有CREATE_WEEKDAY的SUM。在示例中给出 = 111 + 225 = 336

但是这个代码不起作用。我感谢任何帮助。

干杯

Answer 1

这是使用子查询的一种方法

首先确定给定过滤器(where 子句)的总计数

SELECT Count(*) 
FROM   call_view 
WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
       AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
       AND customer_company_name = 'Company' 

然后在分母中使用此查询来查找百分比

SELECT Count(create_weekday), 
       create_weekday, 
       Count(create_weekday) * 100 / (SELECT Count(*) 
                                      FROM   call_view 
                                      WHERE 
       ( create_month = Month(Now() - 
                        INTERVAL 1 month) ) 
       AND ( create_year = Year( 
             Now() - INTERVAL 1 month) ) 
       AND customer_company_name = 
           'Company') 
FROM   call_view 
WHERE  ( create_month = Month(Now() - INTERVAL 1 month) ) 
       AND ( create_year = Year(Now() - INTERVAL 1 month) ) 
       AND customer_company_name = "Company" 
GROUP  BY create_weekday