php - 获取 MySQL 查询中变量的平均值以及 SUMS

Question

(这是我之前问题的重写，可能不够清楚)

我有一个 MYSQL 数据库的查询，如下所示:

SELECT name,
SUM(IF(date_format (date, '%b, %Y')= 'Dec, 2011', 1,0)) AS `month1`, 
SUM(IF(date_format (date, '%b, %Y')= 'Jan, 2012', 1,0)) AS `month2`, 
SUM(IF(date_format (date, '%b, %Y')= 'Feb, 2012', 1,0)) AS `month3`, 
etc...

这给我带来了一系列结果，例如 -month1=55、month2=70、month3=89 等

查询中有一行 -

COUNT(*) AS total FROM table order by total

这实际上给了我总共month1+month2+month3+等

但是我还需要获得相同每月总数的平均值

所以我需要一个 MySQL 函数，它实际上类似于

AVG (month1, month2, month3 etc) 

平均值为 55,70,89

有人可以帮忙吗？

非常感谢

根据要求，完整的查询是 -

SELECT name, 
    SUM(IF(date_format (date, '%b, %Y')= 'Nov, 2011', 1,0))/list*1000 AS `month1`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Dec, 2011', 1,0))/list*1000 AS `month2`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Jan, 2012', 1,0))/list*1000 AS `month3`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Feb, 2012', 1,0))/list*1000 AS `month4`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Mar, 2012', 1,0))/list*1000 AS `month5`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Apr, 2012', 1,0))/list*1000 AS `month6`, 
    SUM(IF(date_format (date, '%b, %Y')= 'May, 2012', 1,0))/list*1000 AS `month7`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Jun, 2012', 1,0))/list*1000 AS `month8`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Jul, 2012', 1,0))/list*1000 AS `month9`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Aug, 2012', 1,0))/list*1000 AS `month10`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Sep, 2012', 1,0))/list*1000 AS `month11`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Oct, 2012', 1,0))/list*1000 AS `month12`, 
    COUNT(*) AS total 
FROM table 
group by name 
order by total 

Answer 1

在您的情况下，您可以使用子查询 -

SELECT name,
  `month1`, `month2`, `month3`
  total,
  (`month1` + `month2` + `month3`) / 3 AS `avg`
FROM
  (SELECT name, 
    SUM(IF(date_format (date, '%b, %Y')= 'Nov, 2011', 1,0))/list*1000 AS `month1`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Dec, 2011', 1,0))/list*1000 AS `month2`, 
    SUM(IF(date_format (date, '%b, %Y')= 'Jan, 2012', 1,0))/list*1000 AS `month3`, 
    COUNT(*) AS total 
  FROM table 
  GROUP BY name 
  ORDER BY total
  ) t

<小时/>

但我建议你使用这样的东西 -

SELECT month, AVG(cnt) cnt FROM
  (SELECT MONTH(DATE) month, COUNT(*) cnt FROM table1 GROUP BY month) t
GROUP BY month WITH ROLLUP

...您只需添加年份支持。