SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table c 
  (
    departmentName varchar(100),
    age integer,
    empID integer
  
  )
insert into c values 
  ('Accounting', 53, 1),
  ('Accounting', 48, 2),
  ('Accounting', 30, null),
  ('Accounting', 71, 6),
  ('IT', 25, 3),
  ('IT', 32, 4),
  ('IT', 51, 5);
7 rows affected

select c.departmentName,
       count(case when c.age > 50 then c.empID end) as count50orOver,
       count(case when c.age <= 49 then c.empID end) as countUnder50
  from c
 group
    by c.departmentName


departmentName
count50orOver
countUnder50


Accounting
2
1

IT
1
2


Warning: Null value is eliminated by an aggregate or other SET operation.