calculate the total points of same column of all Tables

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

Latest Posts