Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SUM SQL

Status
Not open for further replies.
edilsoneto

edilsoneto

Programmer
Jan 2, 2004
4
0
0
BR
Where write SUM??

VALOR
----------------------------------------
5.540000
.000000
.000000
.000000
6.360000

(5 row(s) affected)

not!!!


VALOR ???????????
---------------------------------------- ???????
11.900000 ????????




((((((((???))))))))sum(valor)

SELECT ROUND((TITULO.VR_PAGO/TITULO.VR_PAGAR-1) *ISNULL
((SELECT ISNULL
(SUM(VR_LANCAMENTO),0)
FROM LANCAMENTO_RECIBO
WHERE LANCAMENTO_RECIBO.CD_IMOVEL = RECIBO.CD_IMOVEL AND
LANCAMENTO_RECIBO.DT_REFERENCIA = RECIBO.DT_REFERENCIA AND
LANCAMENTO_RECIBO.NR_RECIBO = RECIBO.NR_RECIBO

),0), 2) as VALOR
FROM TITULO WITH(NOLOCK)
, RECIBO
, IMOVEL
WHERE TITULO.NR_TITULO = RECIBO.NR_TITULO
AND RECIBO.CD_IMOVEL = IMOVEL.CD_IMOVEL
AND TITULO.DT_PAGAMENTO >= '20030801 00:00:00.000'
AND TITULO.DT_PAGAMENTO <= '20030831 00:00:00.000'
AND TITULO.ST_TITULO = 'P'
AND TITULO.NT_TITULO = 'R'
AND TITULO.CD_EMPRESA = 8392.0
AND TITULO.CD_CATEGORIA = 265
AND TITULO.VR_PAGO < (SELECT ISNULL(SUM(VR_LANCAMENTO),0)
FROM RECIBO, LANCAMENTO_RECIBO
WHERE TITULO.NR_TITULO = RECIBO.NR_TITULO AND
LANCAMENTO_RECIBO.CD_IMOVEL = RECIBO.CD_IMOVEL AND
LANCAMENTO_RECIBO.DT_REFERENCIA = RECIBO.DT_REFERENCIA AND
LANCAMENTO_RECIBO.NR_RECIBO = RECIBO.NR_RECIBO)
 
Sort by date Sort by votes
It looks like you're trying to sum the results of the subquery. If you could do that it would look like this:

SELECT
sum( --start sum function
ROUND((TITULO.VR_PAGO/TITULO.VR_PAGAR-1) *ISNULL
((SELECT ISNULL
(SUM(VR_LANCAMENTO),0)
FROM LANCAMENTO_RECIBO
WHERE LANCAMENTO_RECIBO.CD_IMOVEL = RECIBO.CD_IMOVEL AND
LANCAMENTO_RECIBO.DT_REFERENCIA = RECIBO.DT_REFERENCIA AND
LANCAMENTO_RECIBO.NR_RECIBO = RECIBO.NR_RECIBO
),0), 2)
) as VALOR --end sum function
FROM TITULO WITH(NOLOCK)
etc...

but I think you're going to run into this error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

You may have to put your subquery into a temporary table first and add it to your join statements.
 
Upvote 0 Downvote
See Tables.


CREATE TABLE [dbo].[TITULO] (
[NR_TITULO] [decimal](12, 0) NOT NULL ,
[NR_BORDERO] [int] NULL ,
[NT_TITULO] [char] (1) NULL ,
[CD_CATEGORIA] [int] NULL ,
[CD_PESSOA] [decimal](7, 0) NULL ,
[CD_AGENTE] [int] NULL ,
[CD_TP_TITULO] [char] (1) NULL ,
[CD_TP_COBRANCA] [int] NULL ,
[DS_TITULO] [char] (30) NULL ,
[DT_VENCIMENTO] [datetime] NULL ,
[VR_PAGAR] [decimal](13, 2) NULL ,
[PR_DESCONTO] [decimal](5, 2) NULL ,
[PR_JUROS] [decimal](5, 2) NULL ,
[PR_MULTA] [decimal](5, 2) NULL ,
[VR_DADO_DESCONTO] [decimal](13, 2) NULL ,
[DT_EMISSAO] [datetime] NULL ,
[ST_TITULO] [char] (1) NULL ,
[NR_PARCELA] [int] NULL ,
[DT_CANC] [datetime] NULL ,
[DS_MOTIVO_CANC] [varchar] (100) NULL ,
[NOSSO_NUMERO] [varchar] (15) NULL ,
[IN_ACEITO] [char] (1) NULL ,
[DT_DESCONTO] [datetime] NULL ,
[CD_OCORRENCIA] [char] (5) NULL ,
[CD_USUARIO_PRORROGACAO] [decimal](7, 0) NULL ,
[DS_MOTIVO_PRORROGACAO] [varchar] (100) NULL ,
[VR_CREDITO] [decimal](13, 2) NULL ,
[OBS_CREDITO] [varchar] (50) NULL ,
[VR_BASE_MULTA] [decimal](15, 2) NULL ,
[VR_JUROS] [decimal](15, 2) NULL ,
[VR_MULTA] [decimal](15, 2) NULL ,
[VR_PAGO] [decimal](15, 2) NULL ,
[DT_PAGAMENTO] [datetime] NULL ,
[CD_USUARIO_PAGAMENTO] [decimal](7, 0) NULL ,
[CD_AGENTE_EMISSAO] [int] NULL ,
[TP_BOLETO] [char] (2) NULL ,
[IN_ACORDO_PAGO] [char] (1) NULL ,
[IN_PAGAMENTO] [char] (1) NULL ,
[CD_CONTA] [int] NULL ,
[CD_TIPO_PAGAMENTO] [char] (1) NULL ,
[VR_DESCONTO] [decimal](15, 2) NULL ,
[DT_APROVACAO] [datetime] NULL ,
[CD_EMPRESA] [decimal](7, 0) NULL ,
[NR_TITULO_ORIGEM] [decimal](12, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IMOVEL] (
[CD_IMOVEL] [int] NOT NULL ,
[ID_ENDERECO] [int] NULL ,
[CD_PESSOA_CONDOMINIO] [decimal](7, 0) NULL ,
[CD_PESSOA_PROPRIETARIO] [decimal](7, 0) NULL ,
[CD_PESSOA_CONDOMINO] [decimal](7, 0) NULL ,
[CD_PESSOA_IMOBILIARIA] [decimal](7, 0) NULL ,
[ID_ENDERECO_COBRANCA] [int] NULL ,
[NM_VALOR_CONDOMINIO] [decimal](15, 2) NULL ,
[PR_VALOR_CONDOMINIO] [decimal](15, 7) NULL ,
[NM_VAGAS] [int] NULL ,
[DS_IMOVEL] [char] (30) NULL ,
[DS_OBSERVACAO] [varchar] (100) NULL ,
[DT_OCUPANTE] [datetime] NULL
)
GO

CREATE TABLE [dbo].[RECIBO] (
[DT_REFERENCIA] [datetime] NOT NULL ,
[CD_IMOVEL] [int] NOT NULL ,
[NR_RECIBO] [int] NOT NULL ,
[NR_TITULO] [decimal](12, 0) NULL ,
[DT_REPASSE] [datetime] NULL ,
[DS_PROTOCOLO] [char] (20) NULL ,
[ST_RECIBO] [char] (1) NULL
)
GO

 
Upvote 0 Downvote
Table LANCAMENTO_RECIBO


CREATE TABLE [dbo].[LANCAMENTO_RECIBO] (
[NR_LANCAMENTO] [decimal](18, 0) NOT NULL ,
[CD_TP_LANCAMENTO] [int] NULL ,
[VR_LANCAMENTO] [decimal](15, 2) NULL ,
[DS_HISTORICO] [char] (100) NULL ,
[DT_COMPETENCIA] [datetime] NULL ,
[CD_PESSOA_FUNCIONARIO] [decimal](7, 0) NULL ,
[DT_LANCAMENTO] [datetime] NULL ,
[ST_LANCAMENTO] [char] (1) NULL ,
[DT_STATUS] [datetime] NULL ,
[CD_PESSOA_STATUS] [decimal](7, 0) NULL ,
[IN_GERADO_AUTO] [char] (1) NULL ,
[DT_REFERENCIA] [datetime] NOT NULL ,
[CD_IMOVEL] [int] NOT NULL ,
[NR_RECIBO] [int] NOT NULL
)


 
Upvote 0 Downvote
EdilSoneto
No se entiende lo que quieres decir.
Si quieres lo puedes explicar en español que si no te lo
puedo contestar lo puedo traducir para que alguien mas te
ayude.
 
Upvote 0 Downvote
DDR, quero fazer a soma dos valores do select. Pela função SUM, aqui estao os selects e a estrutura das tabelas.



SELECT ROUND((TITULO.VR_PAGO/TITULO.VR_PAGAR-1) *ISNULL
((SELECT ISNULL
(SUM(VR_LANCAMENTO),0)
FROM LANCAMENTO_RECIBO
WHERE LANCAMENTO_RECIBO.CD_IMOVEL = RECIBO.CD_IMOVEL AND
LANCAMENTO_RECIBO.DT_REFERENCIA = RECIBO.DT_REFERENCIA AND
LANCAMENTO_RECIBO.NR_RECIBO = RECIBO.NR_RECIBO

),0), 2) as VALOR
FROM TITULO WITH(NOLOCK)
, RECIBO
, IMOVEL
WHERE TITULO.NR_TITULO = RECIBO.NR_TITULO
AND RECIBO.CD_IMOVEL = IMOVEL.CD_IMOVEL
AND TITULO.DT_PAGAMENTO >= '20030801 00:00:00.000'
AND TITULO.DT_PAGAMENTO <= '20030831 00:00:00.000'
AND TITULO.ST_TITULO = 'P'
AND TITULO.NT_TITULO = 'R'
AND TITULO.CD_EMPRESA = 8392.0
AND TITULO.CD_CATEGORIA = 265
AND TITULO.VR_PAGO < (SELECT ISNULL(SUM(VR_LANCAMENTO),0)
FROM RECIBO, LANCAMENTO_RECIBO
WHERE TITULO.NR_TITULO = RECIBO.NR_TITULO AND
LANCAMENTO_RECIBO.CD_IMOVEL = RECIBO.CD_IMOVEL AND
LANCAMENTO_RECIBO.DT_REFERENCIA = RECIBO.DT_REFERENCIA AND
LANCAMENTO_RECIBO.NR_RECIBO = RECIBO.NR_RECIBO)


Tables

CREATE TABLE [dbo].[TITULO] (
[NR_TITULO] [decimal](12, 0) NOT NULL ,
[NR_BORDERO] [int] NULL ,
[NT_TITULO] [char] (1) NULL ,
[CD_CATEGORIA] [int] NULL ,
[CD_PESSOA] [decimal](7, 0) NULL ,
[CD_AGENTE] [int] NULL ,
[CD_TP_TITULO] [char] (1) NULL ,
[CD_TP_COBRANCA] [int] NULL ,
[DS_TITULO] [char] (30) NULL ,
[DT_VENCIMENTO] [datetime] NULL ,
[VR_PAGAR] [decimal](13, 2) NULL ,
[PR_DESCONTO] [decimal](5, 2) NULL ,
[PR_JUROS] [decimal](5, 2) NULL ,
[PR_MULTA] [decimal](5, 2) NULL ,
[VR_DADO_DESCONTO] [decimal](13, 2) NULL ,
[DT_EMISSAO] [datetime] NULL ,
[ST_TITULO] [char] (1) NULL ,
[NR_PARCELA] [int] NULL ,
[DT_CANC] [datetime] NULL ,
[DS_MOTIVO_CANC] [varchar] (100) NULL ,
[NOSSO_NUMERO] [varchar] (15) NULL ,
[IN_ACEITO] [char] (1) NULL ,
[DT_DESCONTO] [datetime] NULL ,
[CD_OCORRENCIA] [char] (5) NULL ,
[CD_USUARIO_PRORROGACAO] [decimal](7, 0) NULL ,
[DS_MOTIVO_PRORROGACAO] [varchar] (100) NULL ,
[VR_CREDITO] [decimal](13, 2) NULL ,
[OBS_CREDITO] [varchar] (50) NULL ,
[VR_BASE_MULTA] [decimal](15, 2) NULL ,
[VR_JUROS] [decimal](15, 2) NULL ,
[VR_MULTA] [decimal](15, 2) NULL ,
[VR_PAGO] [decimal](15, 2) NULL ,
[DT_PAGAMENTO] [datetime] NULL ,
[CD_USUARIO_PAGAMENTO] [decimal](7, 0) NULL ,
[CD_AGENTE_EMISSAO] [int] NULL ,
[TP_BOLETO] [char] (2) NULL ,
[IN_ACORDO_PAGO] [char] (1) NULL ,
[IN_PAGAMENTO] [char] (1) NULL ,
[CD_CONTA] [int] NULL ,
[CD_TIPO_PAGAMENTO] [char] (1) NULL ,
[VR_DESCONTO] [decimal](15, 2) NULL ,
[DT_APROVACAO] [datetime] NULL ,
[CD_EMPRESA] [decimal](7, 0) NULL ,
[NR_TITULO_ORIGEM] [decimal](12, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IMOVEL] (
[CD_IMOVEL] [int] NOT NULL ,
[ID_ENDERECO] [int] NULL ,
[CD_PESSOA_CONDOMINIO] [decimal](7, 0) NULL ,
[CD_PESSOA_PROPRIETARIO] [decimal](7, 0) NULL ,
[CD_PESSOA_CONDOMINO] [decimal](7, 0) NULL ,
[CD_PESSOA_IMOBILIARIA] [decimal](7, 0) NULL ,
[ID_ENDERECO_COBRANCA] [int] NULL ,
[NM_VALOR_CONDOMINIO] [decimal](15, 2) NULL ,
[PR_VALOR_CONDOMINIO] [decimal](15, 7) NULL ,
[NM_VAGAS] [int] NULL ,
[DS_IMOVEL] [char] (30) NULL ,
[DS_OBSERVACAO] [varchar] (100) NULL ,
[DT_OCUPANTE] [datetime] NULL
)
GO

CREATE TABLE [dbo].[RECIBO] (
[DT_REFERENCIA] [datetime] NOT NULL ,
[CD_IMOVEL] [int] NOT NULL ,
[NR_RECIBO] [int] NOT NULL ,
[NR_TITULO] [decimal](12, 0) NULL ,
[DT_REPASSE] [datetime] NULL ,
[DS_PROTOCOLO] [char] (20) NULL ,
[ST_RECIBO] [char] (1) NULL
)
GO



CREATE TABLE [dbo].[LANCAMENTO_RECIBO] (
[NR_LANCAMENTO] [decimal](18, 0) NOT NULL ,
[CD_TP_LANCAMENTO] [int] NULL ,
[VR_LANCAMENTO] [decimal](15, 2) NULL ,
[DS_HISTORICO] [char] (100) NULL ,
[DT_COMPETENCIA] [datetime] NULL ,
[CD_PESSOA_FUNCIONARIO] [decimal](7, 0) NULL ,
[DT_LANCAMENTO] [datetime] NULL ,
[ST_LANCAMENTO] [char] (1) NULL ,
[DT_STATUS] [datetime] NULL ,
[CD_PESSOA_STATUS] [decimal](7, 0) NULL ,
[IN_GERADO_AUTO] [char] (1) NULL ,
[DT_REFERENCIA] [datetime] NOT NULL ,
[CD_IMOVEL] [int] NOT NULL ,
[NR_RECIBO] [int] NOT NULL
)

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
166
pjw001
pjw001
V
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
205
Volvere
V
agorjest1
  • Locked
  • Question
SQL Conditional Sum
Replies
0
Views
44
agorjest1
agorjest1
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
119
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
102
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top