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

Asked

Viewed 2,857 times

1

I need to add some columns in Mysql, use the sum to make an average and make the columns available for display in a report, I tried to make the sum in the query in Mysql but it did not work, I will try to explain better with a picture of my database, I tried to make the sum using SUM thus:

inserir a descrição da imagem aqui

The sql that picture is like this:

SELECT af.idAfericao,
       af.data, af.produto,
       af.deterUmidade,
       af.deterVal1,
       af.deterVal2,
       af.deterVal3,
       af.deterVal4,
       af.deterVal5,
       SUM(af.deterValMedia) AS SomaVarMedia,
       af.modeloDestilador,
       SUM(af.resultadoDestilacao) AS SomaDestilacao,
       SUM(af.diferenca) AS SomaDiferenca,
       af.ativo,
       cu.descricao,
       cc.nome
  FROM comUnidade cu,
       afericaoAgricola af,
       comColaborador cc
 WHERE af.idUnidade = 6
   AND af.idUnidade = cu.idUnidade
   AND af.ativo = 1
   AND af.classificador = cc.idColaborador
   AND af.data BETWEEN '2016-02-10'
   AND '2016-02-16'
   AND af.deterUmidade='Dickey-John'
   AND af.produto='Soja'
   AND af.modeloDestilador='CA 50'
GROUP BY af.idAfericao
ORDER BY af.data DESC

I wish I could have the result of the columns to display in my report, but I also need the sum of each column to make an average.

This is the model of the report: inserir a descrição da imagem aqui

mysql

  • Do a subquery on the select line only to calculate the sum and average meets you?

  • Do you want the sum to be divided into each line? That is, so that this Sum field is displayed only in the last line of the report in the totals?

  • Hello @Ipacheco, thanks for the reply.

  • Hello @gabrieloliveira, I need a sum of the columns exactly as you said.

  • What software are you using for reporting? I may be mistaken but in terms of query you would have no way to bring a line different from the others, they contain the same fields. In this case you need a line of totals, which is usually done in the application that handles the data. You only wanted this result with a Mysql query?

  • I’m using Dreamweaver CS6 @gabrieloliveira, you mean it would be better to do the sum by Dreamweaver?

  • But will this report be available in some web application? Or do you only need to generate it once?

  • It is generated whenever the user needs @gabrieloliveira.

  • So, but are you building an application to read this data and generate the report? In PHP?

  • If you want a line with SUM only at the end, use UNION to join the list SELECT with the SUM SELECT. Use the same conditions in the WHERE of both.

Show 5 more comments

1 answer

1


For some reason I don’t know and because it’s a legacy code, the table afericaoAgricola has the fields deterValMedia, resultadoDestilacao and diferenca defined as DataType VARCHAR(45), I hadn’t tried for such a detail. To solve my problem I did the following, I had to convert the values stored in these fields by changing the comma by dot, thus:

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

That way I got the values right.

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

That’s how I got the figures right.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.