Somar e agrupar em unica linha

[paulo.chagas 1]

Boa tarde senhores, necessito de uma ajudinha...

 

Preciso deixar uma somente linha para cada cliente, está assim:

 

 

NOMCLI SEQUENCIA_SERV VALOR_SERVICO

CONTROL FLEET LTDA EPP 1 60

CONTROL FLEET LTDA EPP 1 60

GALBAS KLEIN 1 100

GALBAS KLEIN 2 100

 

 

 

 

preciso deixar assim:

 

NOMCLI SEQUENCIA_SERV VALOR_SERVICO

CONTROL FLEET LTDA EPP aqui tanto faz o valor 60

GALBAS KLEIN aqui tanto faz o valor 200

 

 

 

Resumindo, quando o campo SEQUENCIA_SERV da mesma empresa for igual, tenho que somente repetir o valor do campo VALOR_SERVICO (foi o caso do 60)

 

 

 

E quando campo SEQUENCIA_SERV da mesma empresa for diferente, tenho que somar os valores do campo VALOR_SERVICO (100 + 100 = 200)

 

 

 

Meu SQL cru está da seguinte maneira:

 

SELECT  
e085cli.nomcli,
       e140isv.seqisv AS sequencia_serv,
       e140isv.vlrliq AS valor_servico
FROM e085cli e085cli
   JOIN e120ped e120ped  
     ON (e120ped.codcli = e085cli.codcli )
   JOIN e140ipv e140ipv      
ON ( e120ped.codemp = e140ipv.codemp AND e120ped.codfil = e140ipv.codfil AND e120ped.numped = e140ipv.numped)
   LEFT OUTER JOIN e140isv e140isv
ON ( e140isv.codemp = e140ipv.codemp AND e140isv.codfil = e140ipv.codfil AND e140isv.numped = e140ipv.numped  )
WHERE e120ped.codemp = 8 and e120ped.datemi between TO_DATE('26/11/2014','DD/MM/YYYY') and TO_DATE('26/11/2014','DD/MM/YYYY') AND   e120ped.numped IN (814, 837)
order by e120ped.numped

[Motta 645]

Tente

select nomcli,sum(valor_servico)
from
(
SELECT distinct  
e085cli.nomcli,
        e140isv.seqisv AS sequencia_serv,
        e140isv.vlrliq AS valor_servico
FROM e085cli e085cli
    JOIN e120ped e120ped  
      ON (e120ped.codcli = e085cli.codcli )
    JOIN e140ipv e140ipv      
ON ( e120ped.codemp = e140ipv.codemp AND e120ped.codfil = e140ipv.codfil AND e120ped.numped = e140ipv.numped)
    LEFT OUTER JOIN e140isv e140isv
ON ( e140isv.codemp = e140ipv.codemp AND e140isv.codfil = e140ipv.codfil AND e140isv.numped = e140ipv.numped  )
WHERE e120ped.codemp = 8 and e120ped.datemi between TO_DATE('26/11/2014','DD/MM/YYYY') and TO_DATE('26/11/2014','DD/MM/YYYY') AND   e120ped.numped IN (814, 837)
) group by nomcli

[paulo.chagas 1]

 

Tente

select nomcli,sum(valor_servico)
from
(
SELECT distinct  
e085cli.nomcli,
        e140isv.seqisv AS sequencia_serv,
        e140isv.vlrliq AS valor_servico
FROM e085cli e085cli
    JOIN e120ped e120ped  
      ON (e120ped.codcli = e085cli.codcli )
    JOIN e140ipv e140ipv      
ON ( e120ped.codemp = e140ipv.codemp AND e120ped.codfil = e140ipv.codfil AND e120ped.numped = e140ipv.numped)
    LEFT OUTER JOIN e140isv e140isv
ON ( e140isv.codemp = e140ipv.codemp AND e140isv.codfil = e140ipv.codfil AND e140isv.numped = e140ipv.numped  )
WHERE e120ped.codemp = 8 and e120ped.datemi between TO_DATE('26/11/2014','DD/MM/YYYY') and TO_DATE('26/11/2014','DD/MM/YYYY') AND   e120ped.numped IN (814, 837)
) group by nomcli

 

Thanks Motta, vc é o cara, valeu mesmo!!!