nguyenn
asked on
SQL: Sum() question
I need to run a SQL statement to get total values from 2 tables (Table1, Table2)
Select Sum(A.Amount) as AmountA, Sum(B.Amount) as AmountB From Table1 as A, Table2 as B
The above sql is correct if each table has a single record
If Table2 has multiple records, the AmountA seems multiple its total value
Why this SQL causes this error?
thanks
Select Sum(A.Amount) as AmountA, Sum(B.Amount) as AmountB From Table1 as A, Table2 as B
The above sql is correct if each table has a single record
If Table2 has multiple records, the AmountA seems multiple its total value
Why this SQL causes this error?
thanks
probably a better topic for the sql topic area.
ASKER
I run that SQL under Visual Basic environment
Do we have a SQL topic area in here?
thanks
Do we have a SQL topic area in here?
thanks
This looks like it should be two separate commands:
Select Sum(A.Amount) as AmountA From Table1 as A
Select Sum(B.Amount) as AmountB From Table2 as B
To combine the tables, you use something like this:
Select Sum(A.Amount) as AmountA, Sum(B.Amount) as AmountB From Table1 as A, Table2 as B
Where A.Field1 = B.Field1
The where clause joins the tables on a common "foreign key"
If there is no relationship between the tables then you should use two separate selects.
Select Sum(A.Amount) as AmountA From Table1 as A
Select Sum(B.Amount) as AmountB From Table2 as B
To combine the tables, you use something like this:
Select Sum(A.Amount) as AmountA, Sum(B.Amount) as AmountB From Table1 as A, Table2 as B
Where A.Field1 = B.Field1
The where clause joins the tables on a common "foreign key"
If there is no relationship between the tables then you should use two separate selects.
This should solve your problem in one sql statement:
Select (Select Sum(A.Amount) from Table1 as A) as AmountA, Sum(B.Amount) as AmountB From Table2 as B
Select (Select Sum(A.Amount) from Table1 as A) as AmountA, Sum(B.Amount) as AmountB From Table2 as B
ASKER
You re right, I cut off the Where clause
The original likes:
Select Sum(A.Amount + B.Amount) From Table1 as A, Table2 as B Where A.KeyID = B.KeyID
And ....
And....
And....
The original likes:
Select Sum(A.Amount + B.Amount) From Table1 as A, Table2 as B Where A.KeyID = B.KeyID
And ....
And....
And....
ASKER
I think that SQL maybe work if we dont have Where clause, aeklunk.
thanks
thanks
ASKER
Sorry, it works with Clause phrase, but with this SQL we dont have the total of A.Amount + B.Amount
You can add a where clause into the statement:
Select (Select Sum(A.Amount) from Table1 as A WHERE Amount > 10) as AmountA, Sum(B.Amount) as AmountB From Table2 as B WHERE Amount < 10
Give you all sums of amounts greater than 10 in table1 and all amounts less than 10 in table b
of course this kind of sql statement should be used if you don't want to use foreign keys to tie data together.
Select (Select Sum(A.Amount) from Table1 as A WHERE Amount > 10) as AmountA, Sum(B.Amount) as AmountB From Table2 as B WHERE Amount < 10
Give you all sums of amounts greater than 10 in table1 and all amounts less than 10 in table b
of course this kind of sql statement should be used if you don't want to use foreign keys to tie data together.
ASKER
You re right, aeklund. Is there anyway to get the total of 2 amounts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks aeklund, it works exactly as I wish.
Have a great day
nguyenn
Have a great day
nguyenn
Your welcome... enjoy.
or if you want all three totals in one recordset:
Select (Select Sum(A.Amount) from Table1 as A) as AmountA, (Select Sum(B.Amount) as AmountB From Table2 as B), (Select Sum(A.Amount) from Table1 as A) + Sum(B.Amount) as TotalAmount From Table2 as B
this is not an efficient way since you have many selects in one statement... if you don't care that they are all in a recordset it would better to do this:
database connection
recordset connection
Select (Select Sum(A.Amount) from Table1 as A) as AmountA, Sum(B.Amount) as AmountB From Table2 as B
Total = rs(0) + rs(1)
etc.. you get the idea...
Select (Select Sum(A.Amount) from Table1 as A) as AmountA, (Select Sum(B.Amount) as AmountB From Table2 as B), (Select Sum(A.Amount) from Table1 as A) + Sum(B.Amount) as TotalAmount From Table2 as B
this is not an efficient way since you have many selects in one statement... if you don't care that they are all in a recordset it would better to do this:
database connection
recordset connection
Select (Select Sum(A.Amount) from Table1 as A) as AmountA, Sum(B.Amount) as AmountB From Table2 as B
Total = rs(0) + rs(1)
etc.. you get the idea...