ms sql statement

[denny42]

hello all

I have Table1 and table2
table1 design is
Name ,varchar
amount , decimal

table2 design is
Name , varchar
nominal,decimal

how is the statement to find the sum of amount in table1 plus the sum of nominal in table2 ?

thank you

denny

[darkagn]

Hi denny42 and welcome to DaniWeb :)

So is Name the same in each table? ie is it referring to the same thing? If so, you can join the tables on that field like so:

select t1.Name, t1.amount, t2.nominal
from Table1 t1
left outer join Table2 t2 -- left outer join will return all results even if there is no entry in table2 for the corresponding record in table1
on t1.Name = t2.Name -- specifies the column(s) to join on

The above query gives you the Name, amount and nominal columns from your two tables.

Now the SUM() function is used to sum a set of values, and you can use a GROUP BY clause to tell how to sum the values. So the following query will sum the amount column from table 1 for each Name in table 1:

select Name, SUM(amount)
from Table1
group by Name

Ok, there's two pretty big clues as to what you need to do, have a go at combining what I have said and post back if you get stuck.

[denny42]

Hello

no the name not to refer to the same thing because
the name in table1 is for the name of fruits ,the name of table2 is the name of drinks.I want to know how much
money spend for both of them in a month.

thank you and regard