CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2012
AMDI
AMDI

Reputation: 973

Need help in getting count on single column

I need get count on single column from two tables.

Code snippet

select 
    ((select count(Gender) from tableA) +
     (select Count(Gender) from tableB)) as Total Count

select 
    ((select Sum(Case when Gender = M then 1 else 0) from tableA) +    
     (select Sum(Case when Gender = M then 1 else 0) from tableB)) as Male Count

select 
    ((select Sum(Case when Gender = F then 1 else 0) from tableA) +    
     (select Sum(Case when Gender = F then 1 else 0) from tableB)) as Female Count

  1. With these code snippets, I am able to get results as 3 rowsets. Is there any way to get all three counts in one single rowset with three columns (Totalcount, Male count, Female count)?

  2. Other scenario, I need to get count of same columns in the same stored procedure based on different if else conditions. Is there a way to get these counts instead of repeating the same code for every condition?

Upvotes: 0

Views: 37

Answers (1)

Sergey
Sergey

Reputation: 5217

Seems you can try to UNION these two tables and apply aggregation.

SELECT COUNT(GENDER),SUM(CASE WHEN X.GENDER='MALE' THEN 1 ELSE 0 END)AS MALE_COUNT,
  SUM(CASE WHEN X.GENDER='FEMALE' THEN 1 ELSE 0 END)AS FEMALE_COUNT
 FROM
(
  select gender from tablea
  union all
  select gender from tableb  
)AS X

Upvotes: 1

Related Questions