calculate the total points of same column of all Tables

Posted by Vanchi050585 under Sql Server on 4/6/2011 | Points: 10 | Views : 1569 | Status : [Member] | Replies : 4
hi all,

calculate the total points of same column from all tables.

i want to calculate the total points like this forum.
i have column that is "Points" in all tables , more than 10 tables, i want to calculate the total point from all tables for the particular person.

ex:

table1 has 15 points
table2 has 12 points
table3 has 10 points
table4 has 8 points
table5 has 65 points
table6 has 5 points
table7 has 40 points

here i want to calculate the total for the particular person

anybody send the sample code.

Thanks in advance

Thanks
tvn



Responses

Posted by: Jbs.sqlserver on: 7/4/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Try the following code...


--Version : Sql Server 2005 & Above
WITH ctePoint (Point)
AS
(
SELECT Point FROM table1 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table2 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table3 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table4 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table5 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table6 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table7 -- ADD WHERE CONDITION
)SELECT SUM(Point) AS TotalPoints FROM ctePoint

--Version : Sql Server 2000
SELECT SUM (A.Point) AS TotalPoints FROM
(
SELECT Point FROM table1 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table2 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table3 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table4 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table5 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table6 -- ADD WHERE CONDITION
UNION ALL
SELECT Point FROM table7 -- ADD WHERE CONDITION
) A


Regards,
Benil

Thanks & Regards,
Benil

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 7/5/2011 [Member] Starter | Points: 25

Up
0
Down

select (p.points+p1.points+p2.points) as total from table1 p,table2 p1,table3 p2.......


Thanks&Regards
LakshmiNarayana Nalluri.

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/25/2012 [Member] Starter | Points: 25

Up
0
Down
SELECT SUM(TOTALPOINTS) FROM TABLE1,TABLE2,TABLE3

REGARDS

sriram

Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: THU062012 on: 5/25/2012 [Member] Starter | Points: 25

Up
0
Down
If you want to get more materials that related to this topic, you can visit: humanresources.hrvinet.com/accounting-manager-interview-questions/

Best regards.


Vanchi050585, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response