I have a table like this:
orderno insurance
ABC123 3.00
ABC123 3.00
ABC123 3.00
DEF456 2.00
DEF456 2.00
I want to get the sum of the average insurance values for each unique order.
e.g. (3.00+3.00+3.00)/3 + (2.00+2.00)/2
= 5
How can I achieve this using a MySQL query?
try this:
SELECT SUM(avgIns) AS oAverage
FROM
(SELECT OrderNo, AVG(Insurance) AS avgIns
FROM yourTableName
GROUP BY OrderNo) iTable
UPDATE
if you want to limit your decimal places, use 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
iTable is just a name for the subquery.ALIAS, the server will return and error: Error Code: 1248 Every derived table must have its own aliasselect sum(value) from (
select sum(insurance) / count(insurance) as value
from yourTableName
group by insurance
) temp;
Example run in mysql:
mysql> create table testTable (id int primary key auto_increment, orderno varchar(12), insurance decimal(6, 2));
Query OK, 0 rows affected (0.27 sec)
mysql> insert into testTable (orderno, insurance) values ('ABC123', 3), ('ABC123', 3), ('ABC123', 3), ('DEF456', 2), ('DEF456', 2);
Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from testTable;
+----+---------+-----------+
| id | orderno | insurance |
+----+---------+-----------+
| 1 | ABC123 | 3.00 |
| 2 | ABC123 | 3.00 |
| 3 | ABC123 | 3.00 |
| 4 | DEF456 | 2.00 |
| 5 | DEF456 | 2.00 |
+----+---------+-----------+
5 rows in set (0.00 sec)
mysql> select sum(value) from (
select sum(insurance) / count(insurance) as value
from testTable
group by insurance ) temp;
+------------+
| sum(value) |
+------------+
| 5.000000 |
+------------+
1 row in set (0.02 sec)
