How to add two cell values

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

hi guys ~~need urgent help~~!! i have 2 tables and those tables contains some values

SQL

create table PollingBooth_A(
party_id varchar(3) primary key,
No1 int,
No2 int,
No3 int,
No4 int,
No5 int
)

SQL

create table PollingBooth_B(
party_id varchar(3) primary key,
No1 int,
No2 int,
No3 int,
No4 int,
No5 int
)

these two tables are using to store some voting values.....these two are in a seperate database,,,and there is another table called maintable and i want to add PollingBooth_A,PollingBooth_B values to that table as a summary

SQL

create table maintable(
party_id varchar(3) primary key,
No1 int,
No2 int,
No3 int,
No4 int,
No5 int
)

example:

PollingBooth_A
No1 = 12
PollingBooth_B
No1 = 10
then in maintable
No1 = 12+10 = 22

i dnt hv any idea how to do it!! any help?? thank you....

Posted 12-Mar-12 22:12pm

Dilan Shaminda

Updated 12-Mar-12 22:43pm

Herman<T>.Instance

v3

Add a Solution

5 solutions

Solution 2

SQL

Update maintable
set no1= a.no1+b.no1
from PollingBooth_A as 
Inner Join PollingBooth_B as b on b.party_id = a.Party_id
Inner join maintable as mt on mt.party_id = a.Party_id
where mt.party_ID = @party_Id  --declare this parameter so it knows which row has to be updated

Posted 12-Mar-12 22:46pm

Herman<T>.Instance

Solution 3

I have used 3 tables tab1,tab2,tab3 each having col1,col2 as two columns of type int. Modify this according to your tables. i have assumed col1 of both tab1 and tab2 as primary key and sequential values for col1 as 1,2,3,4,5
You may need to remove the loop if taht not the case

SQL

declare @i int
set @i=0
declare @max int
 select @max=count(*) from tab1
while(@i<@max)
BEGIN
declare @sum1 int
declare @sum2 int

select @sum1=a.col1+b.col1 from tab1 a JOIN tab2 b ON a.col1=b.col1 where a.col1=@i
select @sum2=a.col1+b.col1 from tab1 a JOIN tab2 b ON a.col1=b.col1 where a.col1=@i

select a.col2+b.col2 from tab1 a JOIN tab2 b ON a.col1=b.col1
insert into tab3(col1,col2) 
values(@sum1,@sum2)
set @i=@i+1

END

Best of LucK.........!!

Posted 12-Mar-12 23:12pm

Mantu Singh

Comments

N Haque 13-Mar-12 9:07am

you do not need to run loop for that. you can get all rows using UNION

select * from PollingBooth_A
UNION
select * from PollingBooth_B

And then just do sum by party_id.

See Solution 5

Solution 4

Hey,
Try Following if your tables came from different Databases

SQL

INSERT INTO DatabaseName1.maintable(party_id, No1, No2, ...)
  SELECT A.party_id, A.No1 + B.No1, A.No2 + B.No2, ...
    FROM DatabaseName2.PollingBooth_A AS A,
         DatabaseName3.PollingBooth_B AS B
    WHERE A.party_id = B.party_id;

Best luck

Posted 13-Mar-12 0:28am

Nilesh Patil Kolhapur

Solution 5

Hi,
You have to union two tables A and B for getting all rows
And then need to select and sum values group by party_id

SQL

Insert into maintable
	Select party_id,SUM(No1),SUM(No2),SUM(No3),SUM(No3),SUM(No5)
	from 
	(
		select * from PollingBooth_A 
		UNION 
		select * from PollingBooth_B
	) as mt
	group by mt.party_id

After run this Query, you will get all party_id from A and B.
to select the main table

SQL

select * from maintable

Good luck.

Posted 13-Mar-12 3:01am

N Haque

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

E.F. Nijboer · Accepted Answer · 2012-03-12T22:45:00

Solution 1

Try this (don't forget to fill in the ... up until No5):

INSERT INTO maintable(party_id, No1, No2, ...) 
  SELECT A.party_id, A.No1 + B.No1, A.No2 + B.No2, ...
    FROM PollingBooth_A AS A,
         PollingBooth_B AS B
    WHERE A.party_id = B.party_id;

Good luck!

Posted 12-Mar-12 22:45pm

E.F. Nijboer

Updated 12-Mar-12 22:46pm

v2

Comments

Dilan Shaminda 13-Mar-12 11:08am

It works correctly...thanx a lot....we are developing an automated election system for an assignment....in there we have to get votes from separate polling booths and then have to add them up...that's why i asked above question...In a polling division there may be 10-20 polling booths...so how do i add them all?? also there may be more than 1 polling divisions....any suggestion sir? Thank you....

E.F. Nijboer 14-Mar-12 7:43am

It would be way more efficient to gather them into a single table so you can group them. Dividing the results in many different tables is very inefficient if the actual table format is the same and the data are also closely related.