I have a query which does not return result with the desired alias names of columns in PostgreSQL The following is my query which does not return result with the desired alias column names:  

with d as (select distinct emp.ahrims_id, rank.name as rank 
from hr_employee as emp, ana_rank as rank, 
unit_identification_code as uic, record_record as rec
where emp.unit_identificatin_code_id = uic.id and
rec.hr_employee_id = emp.id and
rank.id = emp.rank_id
and uic.code in ('Z00100','Z00200','Z00300','Z00400','Z00450','Z00500','Z00698','Z00600',
'Z00699','Z00680','Z00700','Z00697','Z00800','Z00900','Z01000','Z01100',
'Z01200','Z01300','Z39000','Z39012','Z10000','Z10005','Z10007','Z10009',
'Z10100','Z10101','Z10200','Z10300','Z10301','ZACA97','ZOCG01','ZACA99',
'ZACA98','ZOCG02','Z10400','Z10500','Z10600','Z10700','Z10800','Z11000',
'Z36000','Z37000','Z20000'))
select(select count(*) as "GSG1 Military NCO" from d where rank in 
('MSG','SFC','SGM','SGT','SSG','G1','G2','G3','G4','G5')),
(select count(*) as "GSG1 Military SDR" from d where rank in ('SDR','G6')),
(select count(*) as "GSG1 Civilian NCO" from d where rank in ('Civ11','Civ12','Civ13','Civ14','Civ15')),
(select count(*) as "GSG1 Civilian SDR" from d where rank in ('Civ16'))


It returns:  

  ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
     1615 |      695 |        0 |        1


My desired result:

   GSG1 Military NCO | GSG1 Military SDR | GSG1 Civilian NCO | GSG1 Civilian SDR
---------------------+-------------------+-------------------+------------------
     1615            |      695          |        0          |        1
You have to define the aliases for the columns outside the subqueries:

with d as (
  select distinct emp.ahrims_id, rank.name as rank 
  from hr_employee as emp, ana_rank as rank, 
       unit_identification_code as uic, record_record as rec
  where emp.unit_identificatin_code_id = uic.id and
        rec.hr_employee_id = emp.id and
        rank.id = emp.rank_id and
        uic.code in (
          'Z00100','Z00200','Z00300','Z00400','Z00450','Z00500','Z00698','Z00600',
          'Z00699','Z00680','Z00700','Z00697','Z00800','Z00900','Z01000','Z01100',
          'Z01200','Z01300','Z39000','Z39012','Z10000','Z10005','Z10007','Z10009',
          'Z10100','Z10101','Z10200','Z10300','Z10301','ZACA97','ZOCG01','ZACA99',
          'ZACA98','ZOCG02','Z10400','Z10500','Z10600','Z10700','Z10800','Z11000',
          'Z36000','Z37000','Z20000')
  )
select (select count(*) from d where rank in ('MSG','SFC','SGM','SGT','SSG','G1','G2','G3','G4','G5')) as "GSG1 Military NCO",
       (select count(*) from d where rank in ('SDR','G6')) as "GSG1 Military SDR",
       (select count(*) from d where rank in ('Civ11','Civ12','Civ13','Civ14','Civ15')) as "GSG1 Civilian NCO",
       (select count(*) from d where rank in ('Civ16')) as "GSG1 Civilian SDR"