Somar pontos por empresa
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á!
Discussão (2)
Carregando comentários...