1

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        
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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.