DECLARE @sum1 int
DECLARE @sum2 int
SET @Sum1 = SELECT COUNT(*) FROM Table1
SET @Sum2 = SELECT COUNT(*) FROM Table2
SELECT
@Sum1 As SumTable1,
@Sum2 as SumTable2,
@Sum1 + @Sum2 As Total
or just
SELECT (SELECT COUNT(*) FROM Table_1) As count1,
(SELECT COUNT(*) FROM Table_2) As count2,
(SELECT COUNT(*) FROM Table_1)+(SELECT COUNT(*) FROM Table_2) as total
only the total
SELECT (SELECT COUNT(*) FROM Table_1)+(SELECT COUNT(*) FROM Table_2) as total