How to add column in MySQL using SUM leaving column available for display

Question

How to add column in MySQL using SUM leaving column available for display

Navigation

#1 by (1 votes)

1

I need to add a few columns in MySQL, use the sum to make a mean and leave the columns available for display in a report, I tried to add the query in MySQL but it did not work, I'll try to explain better with an image of my bank, I tried to sum it up using SUM like this:

Thesqlofthisimagelookslikethis:

SELECTaf.idAfericao,af.data,af.produto,af.deterUmidade,af.deterVal1,af.deterVal2,af.deterVal3,af.deterVal4,af.deterVal5,SUM(af.deterValMedia)ASSomaVarMedia,af.modeloDestilador,SUM(af.resultadoDestilacao)ASSomaDestilacao,SUM(af.diferenca)ASSomaDiferenca,af.ativo,cu.descricao,cc.nomeFROMcomUnidadecu,afericaoAgricolaaf,comColaboradorccWHEREaf.idUnidade=6ANDaf.idUnidade=cu.idUnidadeANDaf.ativo=1ANDaf.classificador=cc.idColaboradorANDaf.dataBETWEEN'2016-02-10'AND'2016-02-16'ANDaf.deterUmidade='Dickey-John'ANDaf.produto='Soja'ANDaf.modeloDestilador='CA50'GROUPBYaf.idAfericaoORDERBYaf.dataDESC

IwishIcouldhavetheresultofthecolumnstodisplayinmyreport,butIalsoneedthesumofeachcolumntoaverage.

Thereporttemplateisthis:

mysql

asked by anonymous 03.03.2016 / 20:55

1 answer

How to access a local server from an Android device Send data from a form to a modal window

score 1 · Accepted Answer

For some reason that I do not know and because it is a legacy code, the afericaoAgricola table has the fields deterValMedia , resultadoDestilacao and diferenca defined as DataType VARCHAR(45) , I had not attacked this detail . To solve my problem I did the following, I had to convert the values stored in those fields by changing the comma by point, like this:

SELECT IdAfericao, CAST( REPLACE(afericaoAgricola.deterValMedia,',','.') as DECIMAL(18,2)) FROM afericaoAgricola WHERE afericaoAgricola.IdAfericao <= 20

That way I got the values correctly.

To do the sum I did so: SELECT idAfericao, SUM(CAST( replace(afericaoAgricola.deterValMedia,',','.') as DECIMAL(18,2)) ) FROM afericaoAgricola WHERE afericaoAgricola.idAfericao <= 20

With this I was able to make the values correct.