SELECT SUM MYSQL

Question

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   

She 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 up the repeated Ids that in the above case would be 5590,82. When I use the instruction MYSQL up with the SUM(valor_sap), it returns me by adding the repeated IDS. I tried using Group BY id does not work because it returns everything separately. I can not use DISTINCT value, because there are other records with equal values.

Someone knows what I can do?

Answer 1

I don’t know exactly what problem you have with DISTINCT, but it’s supposed to work perfectly like this with a SELECT external:

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 the DISTINCT internal it only returns the distinct lines, then returns 3 lines as your example.