MySQL-求和和和计数

Question

我想计算每个服务的总和（百分比值）除以特定服务在开始和停止日期之间的所有服务器的平均值，也必须忽略值"nil"

开始日期停止日期连接ftp http imap pop3 smtp ssh2013-11-28 2013-12-05 100 99.89 99.89 100 100

表格

id时间戳服务器服务开始日期停止日期百分比1 2013-12-05 11:46:57 a.martin.sg conn 2013-11-28 2013-12-05 1002 2013-12-05 11:46:57 a.martin.sg ftp 2013-11-28 2013-12-05 1003 2013-12-05 11:46:57 a.martin.sg http 2013-11-28 2013-12-05 1004 2013-12-05 11:46:57 a.martin.sg imap 2013-11-28 2013-12-05无5 2013-12-05 11:46:57 a.martin.sg pop3 2013-11-28 2013-12-05 1006 2013-12-05 11:46:57 a.martin.sg smtp 2013-11-28 2013-12-05 1007 2013-12-05 11:46:57 a.martin.sg ssh 2013-11-28 2013-12-05 1008 2013-12-05 11:46:57 b.martin.sg conn 2013-11-28 2013-12-051009 2013-12-05 11:46:57 b.martin.sg ftp 2013-11-28 2013-12-05 10010 2013-12-05 11:46:57 b.martin.sg http 2013-11-28 2013-12-05 99.9511 2013-12-05 11:46:57 b.martin.sg imap 2013-11-28 2013-12-05无12 2013-12-05 11:46:57 b.martin.sg pop3 2013-11-28 2013-12-05 99.4713 2013-12-05 11:46:57 b.martin.sg smtp 2013-11-28 2013-12-05 99.1914 2013-12-05 11:46:57 b.martin.sg ssh 2013-11-28 2013-12-05 10015 2013-12-05 11:46:57 c.martin.sg conn 2013-11-28 2013-12-05 10016 2013-12-05 11:46:57 c.martin.sg ftp 2013-11-28 2013-12-05 10017 2013-12-05 11:46:57 c.martin.sg http 2013-11-28 2013-12-05 10018 2013-12-05 11:46:57 c.martin.sg imap 2013-11-28 2013-12-05无19 2013-12-05 11:46:57 c.martin.sg pop3 2013-11-28 2013-12-05 10020 2013-12-05 11:46:57 c.martin.sg smtp 2013-11-28 2013-12-05 10021 2013-12-05 11:46:57 c.martin.sg ssh 2013-11-28 2013-12-05 10022 2013-12-05 11:46:57 d.martin.sg conn 2013-11-28 2013-12-05 10023 2013-12-05 11:46:57 d.martin.sg ftp 2013-11-28 2013-12-05 10024 2013-12-05 11:46:57 d.martin.sg http 2013-11-28 2013-12-05 99.8925 2013-12-05 11:46:57 d.martin.sg imap 2013-11-28 2013-12-05无26 2013-12-05 11:46:57 d.martin.sg pop3 2013-11-28 2013-12-05 99.8927 2013-12-05 11:46:57 d.martin.sg smtp 2013-11-28 2013-12-05 99.7828 2013-12-05 11:46:57 d.martin.sg ssh 2013-11-28 2013-12-05 100

当前尝试：

SELECT   service,
         SUM(IF(service = 'http', percentage, 0)) AS 'http',
         SUM(percentage) AS Total
FROM     virtualunix
WHERE    start = '2013-11-28'
GROUP BY service;

+---------+---------+---------+|服务|http|总计|+---------+---------+---------+|连接|0|3392.39||ftp |0 | 2699.68||网址：3399.34||imap|0|900||pop3|0|2897.09||smtp |0|2897.01||ssh |0 | 3399.95|+---------+---------+---------+

Answer 1

除非我误解了，否则您不只是想使用AVG()而不是SUM()吗？

SELECT   service, AVG(percentage)
FROM     virtualunix
WHERE    start_date = '2013-11-28'
GROUP BY service