Combine two SELECT statements from same table with diff. where condition

Question

Suppose we have an Emp table with EmpId, Manager, Subb as three columns.

Manager is 1 when EmpId is manager, similarly for subb.

Require number of manager and subb from table.

Can we combine these two queries into a single SELECT query? (want to scan table just once)

select count(*) as ManagerNumber from Emp where Manager=1
select count(*) as Subordinate  from Emp where Subb=1.

Answer 1

You can do this:

SELECT 
  SUM(CASE WHEN Manager = 1 THEN 1 ELSE 0 END) AS ManagerNumber, 
  SUM(CASE WHEN subb = 1 THEN 1 ELSE 0 END) AS Subordinate 
FROM Emp 

And it is SQL ANSI standard, it will work in all RDBMS.