SELECT SUM MYSQL

Question

SELECT SUM MYSQL

Navigation

#1 by (3 votes)

0

I have the following MYSQL query:

select v.id,valor_sap,month(dthr_operacao) as mes, fornecedor_nome from viagem v                
INNER JOIN agente_viagem av ON v.id = av.viagem_id  
INNER JOIN agente ON av.agente_id = agente.agente_id  
INNER JOIN fornecedor on agente.fornecedor_id = fornecedor.fornecedor_id  
WHERE year(dthr_operacao) = 2015 and agente.fornecedor_id = 3 and month(dthr_operacao) = 2

It returns me the following:

id  |  valor  | mes  |  fornecedor
-----------------------------------
552 | 1439.10 |  2   | FORNECEDOR1  
552 | 1439.10 |  2   | FORNECEDOR1  
314 | 2331.07 |  2   | FORNECEDOR1  
643 | 1820.65 |  2   | FORNECEDOR1  
643 | 1820.65 |  2   | FORNECEDOR1

What I want is the sum of the total, without adding the repeated IDs which in the above case would be 5590,82 . When I use the MYSQL statement above with SUM(valor_sap) , it returns me by adding the repeated IDs. I tried using Group BY id it does not work, because it returns everything separately. I can not use DISTINCT in value, because there are other records with equal values.

Does anyone know what I can do?

mysql

asked by anonymous 12.06.2015 / 03:54

1 answer

How do I assign click effects to an ImageButton? [closed] Apply permissions after running the rsync command

score 3 · Accepted Answer

I'm not exactly sure what the problem is with DISTINCT , but it's supposed to work just fine with an external SELECT :

select SUM(t.valor_sap)  as total
FROM
(
    select DISTINCT v.id,valor_sap,month(dthr_operacao) as mes, fornecedor_nome from viagem v                
    INNER JOIN agente_viagem av ON v.id = av.viagem_id  
    INNER JOIN agente ON av.agente_id = agente.agente_id  
    INNER JOIN fornecedor on agente.fornecedor_id = fornecedor.fornecedor_id  
    WHERE year(dthr_operacao) = 2015 and agente.fornecedor_id = 3 and month(dthr_operacao) = 2
) t

With% internal% it only returns the distinct lines, so it would return 3 lines as its example.