How to add the value of a Column with the Average in mysql

Question

This is my table data the table name is Obat

+---------+---------+----------------+-------+ 
| merek   | formula | nm_obat        | harga | 
+---------+---------+----------------+-------+
| am001   | 1x1     | Antimo         |  3500 | 
| gp002   | 1x1     | Glimipirid     | 20000 | 
| if001   | 1x1     | Inzaflu        |  4500 | 
| mf500   | 3x1     | Metformin500mg | 10000 | 
| mixg001 | 1x1     | Mixagrip       |  5000 | 
+---------+---------+----------------+-------+

How can I add the value in Harga column with the Average of Harga?

This is what I've been trying:

UPDATE obat SET  
harga = harga + (select avg(harga) from obat);

have you tried anything at all? if so, please post your attempt — Vamsi Prabhala, Nov 17 '15 at 21:54
It's considered rude here to post screenshots of data. It makes it harder for those who want to help you to set up a test environment. Use `
` tags to post your data in a text format. — Joel Coehoorn, Nov 17 '15 at 21:55
@JoelCoehoorn I'm sorry, I'm not trying to be rude here but I'm still learning how to post question :D can u help me with my question? — Cici Cici, Nov 17 '15 at 22:14
@cicicici are you trying to permanently change the value or just add the value for display purposes? You're initial question was unclear but your update statement makes me think you want to actually alter data... which would result in a different answer. So I'm unclear of your final goal. — xQbert, Nov 17 '15 at 22:20

score 0 · Accepted Answer · edited May 23 '17 at 10:27

Create a data set consisting of just the average. then cross join it to the base set allowing you to add the two values together. since 1*#records in datatable will equal the same records in data table you'll get the same number of rows.

This approach selects the average once. You could run this select each time by moving it into the select but that is generally slower..

Best approach IMO. (in my opinion)

SELECT A.merek, A.formula, A.nm_obat, A.harga, harga+B.mAvg  as newCol
FROM DataTable A
CROSS JOIN (SELECT avg(harga) mAvg FROM dataTable) B

Alternative approach but much slower.

SELECT A.merek
     , A.formula
     , A.nm_obat
     , A.harga
     , harga+(SELECT avg(harga) mAvg 
              FROM dataTable)  as newCol
FROM DataTable A

to Update it should be this simple: (other examples) mysql update column with value from another table

update obat A
cross join (select avg(harga) mavg from obat) b
Set A.harga = A.Harga+B.Mavg;

Hi @xQbert thanks for answering my question. But I'm still confused with the A. and o. What is that for? — Cici Cici, Nov 17 '15 at 22:14
O was a typo; should have been A. The A and O are table aliases shorthand for referencing tables instead of spelling out the whole names. But this doesn't change the value it simply shows you how you can generate it. — xQbert, Nov 18 '15 at 14:12

How to add the value of a Column with the Average in mysql

1 Answers1