PAMInspection

SELECT --COUNT (RESULT_ID)

  COUNT(

  CASE

    WHEN RESULT_ID IN (1,3,6)

    THEN 'مستوفي '

    ELSE NULL

  END ) mostwfii ,

  COUNT(

  CASE

    WHEN RESULT_ID IN (2,4,5,7)

    THEN 'مخالف '

    ELSE NULL

  END ) mokhlaf ,

  cwi_get_pkg.get_main_activity_sector_desc ( Ins_Pkg.GET_order_licn( vdtl.ORDER_ID ) ) lincc_eco

  -- vdtl.VISIT_ID

FROM INS_VISIT_ORDER_DTL vdtl ,

  INS_VISIT_ORDER vm

WHERE vm.VISIT_ID   = vdtl.VISIT_ID

AND vm.VISIT_STATUS =2

AND LOCATION_STATUS = 3

and VISIT_ID between  :p_from_date to :P_to_Date 

AND RESULT_ID      IS NOT NULL

AND RESULT_ID      <> -1 -- we have 309 visit null result till 11042019 ;

GROUP BY cwi_get_pkg.get_main_activity_sector_desc ( Ins_Pkg.GET_order_licn( vdtl.ORDER_ID ) ) ;