Click here to Skip to main content
15,446,510 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two sql tables wich are :
Torcamentos_new
Fields : cod_orcamento int uniqueidentifier
Some more fields date etc

Torcamento_aux
Fields : cod_orc int
Quantidade int
Preco_unit float
Iva int
Descricao varchar

The records i have in Torcamentos_new are
Cod_orcamento
16
47

The records i have in Torcamento_aux are
Cod_orc quantidade preco_unit iva
16 1 1 23
47 1 1 23
47 1 1 23

The table Torcamento_aux is filtered Torcamento_aux.cod_orc=torcamentos_new.cod_orcamento
Using ado table

Im also using an ado query to sum the total from torcamento_aux
But the sql code im using is calculating the sum of all records without filtering.
The code im using is :

select sum (Quantidade * Preco_unit * Iva /100 + (quantidade * preco_unit)) as 'TotalCIVA1'
from Torcamento_aux, Torcamentos_new
where TOrcamento_aux.Cod_Orc=Torcamentos_new.cod_orcamento;

Showing the result of 2,46 when i was expecting the result of 1,23 in each record.

I have also tried the folowing code :

select sum (Quantidade * Preco_unit * Iva /100 + (quantidade * preco_unit)) as 'TotalCIVA1'
from Torcamento_aux
inner join Torcamentos_new on TOrcamento_aux.Cod_Orc=Torcamentos_new.cod_orcamento;

What it does is the sum of each record properply but it shows two records of the total of each record 1,23 and 1,23

What i need is to show only one record with the total
Each time i scrool on torcamentos_new or add records on torcamento_aux.

Can anyone help me please?

What I have tried:

select sum (Quantidade * Preco_unit * Iva /100 + (quantidade * preco_unit)) as 'TotalCIVA1'
from Torcamento_aux
inner join Torcamentos_new on TOrcamento_aux.Cod_Orc=Torcamentos_new.cod_orcamento;
Posted
Updated 30-Jun-22 20:31pm
Comments
Wendelius 30-Jun-22 16:20pm     CRLF
If I run the query with your example data, the result looks correct TotalCIVA1 3,69 What would you expect it to be?
joao pedro Jun2022 30-Jun-22 16:45pm     CRLF
I expect cod_orc quantidade preco_unit iva totalciva1 16 1 1 23 1,23 47 1 1 23 47 1 1 23 2,46 I need the totalciva1 separetley when i scroll o the table torcamentos_new or add new records on torcamento_aux

1 solution

If I understand the question correctly you can for example use windowing aggregations, common table expression, and LEAD together to achieve the result.

For example the following query
SQL
select cod_orc,
       quantidade,
       preco_unit,
       sum(Quantidade * Preco_unit * Iva /100 + (quantidade * preco_unit))
          over (partition by cod_orc,  quantidade,  preco_unit order by cod_orc,  quantidade,  preco_unit) as TotalCIVA1
from Torcamento_aux, Torcamentos_new
where TOrcamento_aux.Cod_Orc=Torcamentos_new.cod_orcamento

would result to
cod_orc quantidade preco_unit TotalCIVA1
------- ---------- ---------- ----------
16      1          1          1,23
47      1          1          2,46
47      1          1          2,46

The result is quite close but if you want to show the total only on last row you can use LEAD to check if key columns differ on current and next row. For example
SQL
with initdata (cod_orc,  quantidade, preco_unit, TotalCIVA1) as (
   select cod_orc,  
          quantidade,  
	      preco_unit, 
          sum(Quantidade * Preco_unit * Iva /100 + (quantidade * preco_unit)) 
             over (partition by cod_orc,  quantidade,  preco_unit order by cod_orc,  quantidade,  preco_unit) as TotalCIVA1
   from Torcamento_aux, Torcamentos_new
   where TOrcamento_aux.Cod_Orc=Torcamentos_new.cod_orcamento
)
select cod_orc,  
       quantidade,  
	   preco_unit, 
       case 
	      when lead(cod_orc) over (order by  cod_orc,  quantidade,  preco_unit) != cod_orc then TotalCIVA1
	      when lead(cod_orc) over (order by  cod_orc,  quantidade,  preco_unit) is null then TotalCIVA1
		  else null
	   end  as TotalCIVA1
from  initdata

This would give
cod_orc quantidade preco_unit TotalCIVA1
------- ---------- ---------- ----------
16      1          1          1,23
47      1          1          NULL
47      1          1          2,46
 
Share this answer
 
Comments
joao pedro Jun2022 1-Jul-22 5:29am     CRLF
i need only one totalciva1 when i scroll on torcamentos_New filtering torcamento_Aux by Cod_Orcamento=Cod_Orc i need it to show me only one record on query for example when i scroll on cod_Orc = 16 totalciva1 needs to result 1,23 only when i scroll on cod_Orc = 47 totalciva1 needs to result 2,46 only without the null your code works but it has nulls and shows all totals from torcamento_Aux and i need only one totalciva1 each time i scroll or add new records. Can you help me please? thanks
joao pedro Jun2022 1-Jul-22 7:22am     CRLF
can i use a delphi tfilter on the ado query? like i used on the ado table?
joao pedro Jun2022 1-Jul-22 8:41am     CRLF
the filter i have on ado table is : procedure TForm1.ADOTorcamentosAfterScroll(DataSet: TDataSet); begin if (form1.Active=True) then begin If (ADOTOrc_AuxCod_Orc.AsString <>'') and (ADOTOrcamentosCod_orcamento.AsString <>'') then begin ADOTorc_Aux.Filtered:=false; Edit1.Text:=ADOTorcamentosCod_Orcamento.AsString; ADOTorc_Aux.Filter := ('[Cod_Orc]=' + QuotedStr(Edit1.Text)); ADOTorc_Aux.Filtered:=true; end; end; end; Can i use the same kind of filter on tado query?
Wendelius 1-Jul-22 13:56pm     CRLF
Earlier you wrote that the expected result is
I expectcod_orc quantidade preco_unit iva totalciva116 1 1 23 1,2347 1 1 2347 1 1 23 2,46
If that's not the case can you post an example what is the expected result?
joao pedro Jun2022 1-Jul-22 14:47pm     CRLF
i expect when i scroll on torcamentos_New with cod_orcamento = 16 table torcamento_aux cod_Orc quantidade preco_unit iva totalciva1 1,23 when i scrolll on torcamentos_new with cod_Orcamento = 47 table torcamento_aux cod_Orc quantidade preco_unit iva totalciva1 2,46 i need only one record on the Ado query when i scroll o torcamento_new or add records on torcamento_aux

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900