select  DES_DAM, 
     count(num1) patrons_capitaines,
     count(num2) chef_mecanicien,
     count(num3) second_patron,
     count(num4) secon_mecanicien,
     count(num5) lieutenant_pont,
      count(num6) lieutenant_machine,
       count(num7) matelot
from (                                                              
SELECT DES_DAM                                                       
, case when fonction.des_fon in ('PATRON','CAPITAINE') then marin.num_ins end  num1
, case when fonction.des_fon= 'CHEF MECANICIEN' then marin.num_ins end  num2
, case when fonction.des_fon= 'SECOND PATRON' then marin.num_ins  end num3
, case when fonction.des_fon= 'SECOND MECANICIEN' then marin.num_ins  end num4
, case when fonction.des_fon= 'LIEUTENANT PONT' then marin.num_ins  end num5
, case when fonction.des_fon= 'LIEUTENANT MACHINE' then marin.num_ins  end num6
, case when fonction.des_fon= 'MATELOT' then marin.num_ins  end num7
                 FROM fonction, marin, qua_marin, qualification,fon_qualification,dam 
                 where  qua_marin.num_ins=marin.num_ins and qua_marin.cod_qua=qualification.cod_qua
                            and qualification.cod_qua=fon_qualification.cod_qua and fon_qualification.cod_fon=fonction.cod_fon
                             and dam.num_dam=marin.num_dam and dam.num_sou_dam=marin.num_sou_dam ) 
group  by  DES_DAM;