In the project I'm working I use php language and mysql database.
The reason I thought to use temporary tables was right is because I perform many calculations, and in javascript they would take me a long time.
I must show these tables at screen:.
Table 1:
Total data. | 6 Average. | 2984-99 Sum x-Media. | 5088.9 Sum (x-Media)^2. | 138092659.8396 Sum (x-Media)^4. | 1.09E+16 Maximum value. | 9812.12 Minimum value. | 18.23
Table 2:
Amount | x-Media | (x-Media)^2 | (x-Media)^4 839.12 | -2145.87 | 704122.3744 | 495788318130.694 18.23 | 18.23 | 332.3329 | 110445.15642241 9812.12 | 11957.99 | 96277698.8944 | 9.27E+15 23.93 | 5.7 | 572.6449 | 327922.18149601 863.21 | -11094.78 | 745131.5041 | 555220958402.328 6353.33 | 6347.63 | 40364802.0889 | 1.63E+15
All calculations are based on the amount column, this value. I get it from the table tbl_layout_c:
Amount decimal (18,2)
----------
839.12
18.23
9812.12
23.93
863.21
6353.33
So the query is:
1 SELECT amount
2 (SELECT COUNT(*) FROM tbl_layout_c ) AS total_data
3 (SELECT SUM(amount) FROM tbl_layout_c ) AS total_sum,
4 (SELECT total_sum / total_data AS average,
5 (SELECT amount - average ) AS x_media,
6 (SELECT SUM(x_media)) AS suma_x_media
7 FROM tbl_layout_c
The issue is at LINE 6: (SELECT SUM(x_media)) AS suma_x_media because I get the same value as x_media column. Ex:
Amount | x-Media | Sum x-Media.
839.12 | -2145.87 | -2145.87
And I need:
Amount | x-Media | Sum x-Media.
839.12 | -2145.87 | 5088.9
I have trouble when I try to use function sum() in the temporary column (suma_x_media) of the temporary table.. It's not working, I just get the same value as x_media
Anyone have any idea what I'm missing?
try this
select *,sum(x_media) from (SELECT monto, #obtengo el campo monto
(SELECT COUNT(*) FROM tbl_layout_c ) AS total_datos, #contar los registros
(SELECT SUM(monto) FROM tbl_layout_c ) AS suma_total, #suma total
(SELECT MAX(monto) FROM tbl_layout_c ) AS valor_maximo, #valor maximo
(SELECT MIN(monto) FROM tbl_layout_c ) AS valor_minimo, #valor minimo
(SELECT suma_total / total_datos) AS promedio, #promedio
(SELECT monto - promedio) AS x_media #media artimetica
FROM tbl_layout_c)tablealias
I tried in sql Fiddle with your data and It works. your sum(x_media) is 0 with data in sql Fiddle you have if you change the data same as here in TABLE 2 then you can get expected output same mentioned in Table 1.