Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
greetings,

I am trying to sum from one table filtering from another table
the code i have is as following :

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;


what happends is that the sum is correct but its the sum of all records in table TOrcamento_Aux its not filtering from table Torcamentos_New,
can anyone help me please?

OP comment with further detail (attached to a non-solution, may disappear so repeating it here)
The table Torcamentos_new
Has two records
Cod_orcamento = 16 and 47
The table Torcamento_aux
Also has two records
Cod_orc = 16 and 47
Quantidade = 1 and 1
Preco_unit = 1 and 1
Iva = 23 and 23
Some records have been deleted for me to test
Table Torcamento_aux is filtered by Torcamento_aux.cod_orc and Torcamentos_new.cod_orcamento using an Edit.text with the Torcamentos_new.cod_orcamento record, the filter is on afterscrool event of the table Torcamentos_new.
This is what links both tables the cod record on both tables.
I have tried the inner join code and the result os the same 'totalciva'=2,46 what do i need to do please?

What I have tried:

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;
Posted
Updated 29-Jun-22 6:41am
v2

The style of join you are using is very old-fashioned, can become difficult to read and restricts you to only being able to use INNER joins. So instead of
SQL
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;
do this instead
SQL
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;
Use an ON clause to define the JOIN and a WHERE clause to define any actual filters.

If I was being honest I would probably also use table aliases in case the query is going to become more complex and explicitly indicate which table the columns I am using come from (to make the query more readable to others)
SQL
select sum (aux.Quantidade * aux.Preco_unit * aux.Iva /100 + (aux.quantidade * aux.preco_unit)) as 'TotalCIVA1' 
from Torcamento_aux aux
inner join Torcamentos_new tnew on aux.Cod_Orc=tnew.cod_orcamento;
If you are getting all of the records from TOrcamento_aux then there must be a row on Torcamentos_new for every row in TOrcamento_aux - that is how an inner join works. Perhaps you need to check some other value. Some sample data might help us to help you further

Edit after OP comments to another solution:
joao pedro Jun2022 commented:
The table Torcamentos_new Has two records
Cod_orcamento = 16 and 47
The table Torcamento_aux Also has two records
Cod_orc = 16 and 47
Quantidade = 1 and 1
Preco_unit = 1 and 1
Iva = 23 and 23
As I suggested - the table Torcamentos_new has rows that match to every row in Torcamento_aux - therefore you will get every record from Torcamento_aux returned in your results. This article might help you understand why - Visual Representation of SQL Joins[^]
joao pedro Jun2022 commented:
Some records have been deleted for me to test
I suggest those records are put back into Torcamento_aux so you can see what is actually happening.
joao pedro Jun2022 commented:
Table Torcamento_aux is filtered by Torcamento_aux.cod_orc and Torcamentos_new.cod_orcamento using an Edit.text with the Torcamentos_new.cod_orcamento record, the filter is on afterscrool event of the table Torcamentos_new.
There is no such thing as Edit.text nor an afterscroll event in SQL. This makes no sense. The "filtering" takes place as part of the JOIN or as the result of a WHERE clause - not because a user has scrolled down in some secret software.
joao pedro Jun2022 commented:
I have tried the inner join code and the result os the same 'totalciva'=2,46 what do i need to do please?
The result will be the same because both tables have the same keys in them. Try adding a row to the Torcamento_aux table that does not have a corresponding row in Torcamentos_new and you should be able to see the difference. There is nothing to do, the answer is correct - what were you expecting to see?

Edit 2 after further comment:
The term "filter" usually means to reduce the number of items based on some criteria.

In this case you mean GROUP BY see GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]

Try
SQL
select aux.Cod_orc,sum (aux.Quantidade * aux.Preco_unit * aux.Iva /100 + (aux.quantidade * aux.preco_unit)) as 'TotalCIVA1' 
from Torcamento_aux aux
inner join Torcamentos_new tnew on aux.Cod_Orc=tnew.cod_orcamento
GROUP BY aux.Cod_Orc;
 
Share this answer
 
v3
Comments
joao pedro Jun2022 29-Jun-22 13:39pm    
i have tryed with this code
select sum (torcamento_aux.Quantidade * torcamento_aux.Preco_unit * torcamento_aux.Iva /100 + (torcamento_aux.quantidade * torcamento_aux.preco_unit)) as 'TotalCIVA1'
from Torcamento_aux
inner join Torcamentos_new on
TOrcamento_aux.Cod_Orc=Torcamentos_new.cod_orcamento
where TOrcamento_aux.Cod_orc=Torcamentos_new.cod_orcamento;
and i was expecting to see 1,23 of totalciva1 in each record and it shows the total of both records 2,49.
I cant use diferent keys, i wont be able to filter Torcamento_Aux.cod_Orc with Torcamentos_New.Cod_Orcamento
and i need to filter so the aplication works as its needed...
CHill60 30-Jun-22 2:51am    
I've updated my solution
joao pedro Jun2022 29-Jun-22 13:42pm    
Im using edit1.text with keys from Torcamento_New.cod_Orcamento
to filter in sql TAdo Table and it works The TAdo Query is not working properly because is not filtering only doing the sum
joao pedro Jun2022 30-Jun-22 6:27am    
I have used group by code you mentioned,
It works showing the correct total but it adds fields to the tado query with diferent values now i have added one more record on torcamento_aux and it shows on a dbgrid two totals with diferent values, the values are correct, how do i do to only have one field on the tado query? Each time i scrool on records or add new ones?
CHill60 30-Jun-22 9:05am    
Scrolling is not relevant! My query cannot possibly add fields - the only field I added was Cod_orc so you can tell which calculated value belongs to which row. Try stating CLEARLY what you are expecting to see - there is no substitute for supplying sample data and expected results when trying to get an answer to a problem.
Focus on the results of your query and not on how you are displaying them. If the results are correct but the display is not, then the problem is in your secret code in the secret language in the application you have not mentioned at all
Without knowing what your tables look like, and what links the two tables (so that row 14 of table1 matches to row 77 of table2 for example) we can't be specific.

But probably, what you need to do is use a JOIN: SQL Joins[^]
 
Share this answer
 
Comments
joao pedro Jun2022 29-Jun-22 13:45pm    
the tables look like this
Table TOrcamentos_New
Cod__orcamento Int Uniqueidentifier
some other fileds
Table TOrcamento_Aux
Cod_Orc Int
Quantidade Int
Preco_Unit Float
Iva Int
some other fields
im using the code

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

its doing only the sum of all records its not filtering by table key
The tables looks like this

Torcamentos_new
Cod_orcamento int uniqueidentifier

Torcamento_aux
Cod_orc int

What links the tables are both Torcamentos_new.cod_orcamento and Torcamento.cod_orc have the same record in field

How do i do a join using my sql code?
 
Share this answer
 
Comments
CHill60 29-Jun-22 8:55am    
You are already doing a join, just an implicit one. See my solution.
You should have updated your original post with this information OR responded to Solution 1 by using the "Have a Question or Comment?" link. "Solutions" are for answers to questions only, not conversations
joao pedro Jun2022 29-Jun-22 11:55am    
The table Torcamentos_new
Has two records
Cod_orcamento = 16 and 47
The table Torcamento_aux
Also has two records
Cod_orc = 16 and 47
Quantidade = 1 and 1
Preco_unit = 1 and 1
Iva = 23 and 23
Some records have been deleted for me to test
Table Torcamento_aux is filtered by Torcamento_aux.cod_orc and Torcamentos_new.cod_orcamento using an Edit.text with the Torcamentos_new.cod_orcamento record, the filter is on afterscrool event of the table Torcamentos_new.
This is what links both tables the cod record on both tables.
I have tried the inner join code and the result os the same 'totalciva'=2,46 what do i need to do please?
CHill60 29-Jun-22 12:55pm    
I have updated my solution to hopefully clarify what is happening
select sum (torcamento_aux.Quantidade * torcamento_aux.Preco_unit * torcamento_aux.Iva /100 + (torcamento_aux.quantidade * torcamento_aux.preco_unit)) as 'TotalCIVA1' 
from Torcamento_aux 
inner join Torcamentos_new on torcamento_aux.Cod_Orc=torcamentos_new.cod_orcamento;
 
Share this answer
 
Comments
CHill60 29-Jun-22 12:39pm    
Is this meant to be a solution?
joao pedro Jun2022 29-Jun-22 15:31pm    
i did a code you mentioned its not filtering only doing the sum of all recordsthe code is this

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

can you help me please?

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