Click here to Skip to main content
15,878,871 members
Search within:


How so I sum and filter two SQL tables on delphi 7 using ado query
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
server
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
Add a Solution
Comments
Wendelius 30-Jun-22 16:20pm    
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    
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
 
Posted
Comments
joao pedro Jun2022 1-Jul-22 5:29am    
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    
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    
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    
Earlier you wrote that the expected result is

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

If that's not the case can you post an example what is the expected result?
joao pedro Jun2022 1-Jul-22 14:47pm    
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
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How so I sum and filter from two tables in SQL server 2012
Ado query filtering problem in delphi
Query for sum(), GROUP by
Sum Of Two Tables Using Join
Add two query different tables to one query and one table
Sql query to filter data in HTML table
Selecting recordes using two tables - Nested SQL query
How to get two results for the SQL query
how to sum the two different table value in sql server
Use sum in query of 2 tables of 2 databases

Advertise
Privacy
Cookies
Terms of Use
Last Updated 1 Jul 2022
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-04-02:1

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