CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 4728

mysql select query using calculated column values

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?

Upvotes: 0

Views: 4556

Answers (2)

jka6510
jka6510

Reputation: 826

select 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)

Upvotes: 0

John Woo
John Woo

Reputation: 263703

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

Upvotes: 2

Related Questions