CODEWITHSUNDEEP
c#sql-server
Nag
Nag

Reputation: 509

How to get the total count of two columns from two different tables in SQL Server?

I have 2 tables in SQL Server, Table1 and Table2.

Table1 contains the information of family head with his age and Table2 contains the information of family members with their age.

I want to get total count of age. Means if the user enter less than 23 in front end so I will display to user total count of persons less than 23 age from two tables.

My approach is like this:

alter procedure example 
   (@age int, @result2 int output)
as
begin
    declare @result int;
    declare @result1 int;

    set @result = (select count(age) from Table1 where age > @age)
    set @result1 = (select count(age) from Table2 where age > @age)

    set @result2 = @result + @result1;
end

hvcid is the primary key column in my Table1 and hvcid is a foreign key in my Table2.

My query shown above returns the result fine. Why not do using join or single query to combine two tables and get the total count of age from two tables?

I don't know how to write using single query? Can you solve my issue?

Upvotes: 0

Views: 1003

Answers (3)

khaled4vokalz
khaled4vokalz

Reputation: 958

Try this @Nag 

select (COUNT(t1.age) + COUNT(t2.age)) as Result
from Table1 t1 
FULL JOIN Table2 t2 on t1.hvcid = t2.hvcid
where t1.age > @age and t2.age > @age

Upvotes: 1

Aliasgar Rajpiplawala
Aliasgar Rajpiplawala

Reputation: 656

You can use below query to get result from 2 tables :

select count(age) + (select count(age) 
    from Table2 where age > @age) 
from Table1 where age > @age

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93754

Another way 

SELECT Sum(cnt) AS total
FROM   (SELECT Count(age) cnt
        FROM   Table1
        WHERE  age > @age
        UNION ALL
        SELECT Count(age)
        FROM   Table2
        WHERE  age > @age) a

Upvotes: 1

Related Questions