Somar pontos por empresa

[paulo.chagas 1]

Boa tarde pessoal, tenho o seguinte SQL

 

SELECT
       e085cli.codgre AS Grupo,
       e085cli.nomcli AS Razao,
       e085cli.apecli AS Nome,
       e085cli.usu_siterevofi AS Site,
       e085cli.usu_foracap AS Capital,
       e085cli.usu_servico Servico,
       e085cli.usu_fachada AS Fachada,
       e085cli.usu_cartafian AS Carta,
       Sum(e140ipv.qtdfat * e075pro.usu_ponfidel) AS pontos
 FROM e085cli e085cli
     JOIN e140nfv e140nfv ON ( e085cli.codcli = e140nfv.codcli )
     JOIN e085hcl e085hcl ON ( e085hcl.codcli = e140nfv.codcli AND e085hcl.codemp = e140nfv.codemp AND e085hcl.codfil = e140nfv.codfil )
     JOIN e140ipv e140ipv ON ( e140nfv.codemp = e140ipv.codemp AND e140nfv.numnfv = e140ipv.numnfv AND e140nfv.codsnf = e140ipv.codsnf )
     JOIN e140ide e140ide ON ( e140ide.codemp = e140ipv.codemp AND e140ide.numnfv = e140ipv.numnfv AND e140ide.codsnf = e140ipv.codsnf )
     JOIN e075pro e075pro ON ( e075pro.codemp = e140ipv.codemp AND e075pro.codpro = e140ipv.codpro )
WHERE e140nfv.datemi BETWEEN To_date('01/10/2014', 'DD/MM/YYYY') and To_date('30/11/2014', 'DD/MM/YYYY')
AND e085cli.nomcli like Upper ('%newland%')
AND e140ipv.codpro NOT LIKE ('10.%')
AND e140ide.sitdoe = 3                                                                                
GROUP BY  e085cli.codgre,
                   e085cli.nomcli,
                   e085cli.apecli,
                   e085cli.usu_siterevofi,
                   e085cli.usu_foracap,
                   e085cli.usu_servico,
                   e085cli.usu_fachada,
                   e085cli.usu_cartafian,
                   e085cli.usu_crepon
ORDER BY  e085cli.codgre desc,
         e085cli.usu_siterevofi DESC,
         e085cli.usu_foracap DESC,
         e085cli.usu_servico DESC,
         e085cli.usu_fachada DESC,
         e085cli.usu_cartafian DESC

 

ele resulta nisso:

 

 

GRUPO RAZAO NOME SITE CAPITAL SERVICO FACHADA CARTA PONTOS TOTAL_PONTOS
362 VEICULOS LTDA LOJA I 0 150 50 0 0 25 ?
362 VEICULOS LTDA LOJA II 0 0 50 0 0 60 ?
362 VEICULOS LTDA LOJA III 0 0 0 50 0 0 16 ?
362 VEICULOS LTDA LOJA IV0 0 0 50 0 0 95 ?

 

Preciso da ultima coluna, "TOTAL_PONTOS"...só que essa soma não vem somente da soma por linha...

A coluna CAPITAL contém valor 150, então terei de somar esses '150' para todas as linhas...a soma para chegar

ao TOTAL_PONTOS terá que ser esse abaixo

 

 

GRUPO RAZAO NOME SITE CAPITAL SERVICO FACHADA CARTA PONTOS TOTAL_PONTOS
362 VEICULOS LTDA LOJA I 0 150 50 0 0 25 0+150+ 50+0+0+25 362 VEICULOS LTDA LOJA II 0 0 50 0 0 60 0+150+ 50+0+0+60
362 VEICULOS LTDA LOJA III 0 0 0 50 0 0 16 0+150+ 50+0+0+16
362 VEICULOS LTDA LOJA IV0 0 0 50 0 0 95 0+150+ 50+0+0+95

 

Todas as linhas teriam que somar com o 150, tem como???

Obrigado desde já!

[Motta 645]

http://www.oracle.com/technetwork/issue-archive/index-1429427.html

 

 

 

As ANALYTICS FUNCTIONS do Oracle fazem fácil estes totais acumulados, veja os artigos.

[paulo.chagas 1]

http://www.oracle.com/technetwork/issue-archive/index-1429427.html

 

 

 

As ANALYTICS FUNCTIONS do Oracle fazem fácil estes totais acumulados, veja os artigos.

 

Thanks Motta, resolvi com

MAX(usu_foracap) OVER (PARTITION BY codgre) AS pontos