mysql使用计算列值选择查询

Question

我有一个这样的表:

orderno       insurance
ABC123        3.00
ABC123        3.00
ABC123        3.00
DEF456        2.00
DEF456        2.00

我想获取每个唯一订单的平均保险值(value)之和。

例如(3.00+3.00+3.00)/3 + (2.00+2.00)/2

= 5

如何使用 MySQL 查询实现此目的？

Answer 1

试试这个:

SELECT SUM(avgIns) AS oAverage
FROM
    (SELECT OrderNo, AVG(Insurance) AS avgIns
    FROM yourTableName
    GROUP BY OrderNo) iTable

更新

如果您想限制小数位数，请使用FORMAT(value,decimalPlaces)

SELECT FORMAT(SUM(avgIns),2) AS oAverage         -- Returns two decimal places
FROM
    (SELECT OrderNo, AVG(Insurance) AS avgIns
    FROM yourTableName
    GROUP BY OrderNo) iTable