WITH sums as (

SELECT hfat."admin1" as "Province",
       hfat."admin2" as "District",
       hfat."admin4" as "Health Facility",
       
      
      
       
       (case when hfat."corticosteroids"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."corticosteroids"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."corticosteroids"='stocked-out' then 'Currently stocked-out'
             when hfat."corticosteroids"='never_available' then 'Never available'
       end) as "Corticosteroids",
       
       SUM((case when hfat."corticosteroids"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Corticosteroids",
       
       (case when hfat."adrenaline"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."adrenaline"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."adrenaline"='stocked-out' then 'Currently stocked-out'
             when hfat."adrenaline"='never_available' then 'Never available'
       end) as "Adrenaline",
       SUM((case when hfat."adrenaline"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Adrenaline",
       
       (case when hfat."frusemide"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."frusemide"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."frusemide"='stocked-out' then 'Currently stocked-out'
             when hfat."frusemide"='never_available' then 'Never available'
       end) as "Frusemide",
       SUM((case when hfat."frusemide"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Frusemide",
       
       (case when hfat."sodium_bicarb"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."sodium_bicarb"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."sodium_bicarb"='stocked-out' then 'Currently stocked-out'
             when hfat."sodium_bicarb"='never_available' then 'Never available'
       end) as "Sodium bicarbonate injection",
       SUM((case when hfat."sodium_bicarb"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Sodium bicarbonate injection",
       
       (case when hfat."diazapam"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."diazapam"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."diazapam"='stocked-out' then 'Currently stocked-out'
             when hfat."diazapam"='never_available' then 'Never available'
       end) as "Diazepam injection",
       SUM((case when hfat."diazapam"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Diazepam injection",
       
       (case when hfat."tetanus"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."tetanus"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."tetanus"='stocked-out' then 'Currently stocked-out'
             when hfat."tetanus"='never_available' then 'Never available'
       end) as "Tetanus toxoid 1 ampoule",
       SUM((case when hfat."tetanus"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Tetanus toxoid 1 ampoule",
       
       (case when hfat."amoxicillin"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."amoxicillin"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."amoxicillin"='stocked-out' then 'Currently stocked-out'
             when hfat."amoxicillin"='never_available' then 'Never available'
       end) as "Amoxicillin",
       SUM((case when hfat."amoxicillin"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Amoxicillin",
       
       (case when hfat."metronidazole"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."metronidazole"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."metronidazole"='stocked-out' then 'Currently stocked-out'
             when hfat."metronidazole"='never_available' then 'Never available'
       end) as "Metronidazole",
       SUM((case when hfat."metronidazole"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Metronidazole",
       
       (case when hfat."ciprofloxacin"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."ciprofloxacin"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."ciprofloxacin"='stocked-out' then 'Currently stocked-out'
             when hfat."ciprofloxacin"='never_available' then 'Never available'
       end) as "Ciprofloxacin",
       SUM((case when hfat."ciprofloxacin"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Ciprofloxacin",
       
       (case when hfat."paracetamol"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."paracetamol"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."paracetamol"='stocked-out' then 'Currently stocked-out'
             when hfat."paracetamol"='never_available' then 'Never available'
       end) as "Paracetamol",
       SUM((case when hfat."paracetamol"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Paracetamol",
       
       (case when hfat."nsaid"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."nsaid"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."nsaid"='stocked-out' then 'Currently stocked-out'
             when hfat."nsaid"='never_available' then 'Never available'
       end) as "Non-steroidal anti-inflammatory drugs ",
       SUM((case when hfat."nsaid"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Non-steroidal anti-inflammatory drugs ",
       
       (case when hfat."opioid_medication"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."opioid_medication"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."opioid_medication"='stocked-out' then 'Currently stocked-out'
             when hfat."opioid_medication"='never_available' then 'Never available'
       end) as "Opioid pain medication",
       SUM((case when hfat."opioid_medication"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Opioid pain medication",
       
       (case when hfat."local_an"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."local_an"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."local_an"='stocked-out' then 'Currently stocked-out'
             when hfat."local_an"='never_available' then 'Never available'
       end) as "Local anaesthetic",
       SUM((case when hfat."local_an"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - Local anaesthetic",
       
       (case when hfat."saline_solution"='in_stock_sufficient' then 'Currently in stock in sufficient quantities'
             when hfat."saline_solution"='in_stock_not_sufficient' then 'Currently in stock but NOT in sufficient quantities'
             when hfat."saline_solution"='stocked-out' then 'Currently stocked-out'
             when hfat."saline_solution"='never_available' then 'Never available'
       end) as "IV Saline Solution",
       SUM((case when hfat."saline_solution"='in_stock_sufficient' then '1'
             else '0'
       end)::integer) as "Score - IV Saline Solution"
       
FROM hfat
WHERE 1=1 
      [[and {{county}}]]
      [[and {{district}}]]
      [[and {{facilty}}]]
      [[and {{date_ass}}]]
GROUP BY hfat."admin1", hfat."admin2", hfat."admin4", 
hfat."corticosteroids",  hfat."adrenaline", hfat."frusemide", hfat."sodium_bicarb", hfat."diazapam", hfat."tetanus", hfat."amoxicillin", hfat."metronidazole",
hfat."ciprofloxacin",  hfat."paracetamol", hfat."nsaid", hfat."opioid_medication", hfat."local_an", hfat."saline_solution"
ORDER BY hfat."admin1" ASC, hfat."admin2" ASC, hfat."admin4" ASC
)
SELECT "Province",
       "District",
       "Health Facility",
       "Corticosteroids",
       "Score - Corticosteroids",
       "Adrenaline",
       "Score - Adrenaline",
       "Frusemide",
       "Score - Frusemide",
       "Sodium bicarbonate injection",
       "Score - Sodium bicarbonate injection",
       "Diazepam injection",
       "Score - Diazepam injection",
       "Tetanus toxoid 1 ampoule",
       "Score - Tetanus toxoid 1 ampoule",
       "Amoxicillin",
       "Score - Amoxicillin",
       "Metronidazole",
       "Score - Metronidazole",
       "Ciprofloxacin",
       "Score - Ciprofloxacin",
       "Paracetamol",
       "Score - Paracetamol",
       "Non-steroidal anti-inflammatory drugs ",
       "Score - Non-steroidal anti-inflammatory drugs ",
       "Opioid pain medication",
       "Score - Opioid pain medication",
       "Local anaesthetic",
       "Score - Local anaesthetic",
       "IV Saline Solution",
       "Score - IV Saline Solution",
       
       "Score - Corticosteroids"
       + "Score - Adrenaline"
       + "Score - Frusemide"
       + "Score - Sodium bicarbonate injection"
       + "Score - Diazepam injection"
       + "Score - Tetanus toxoid 1 ampoule"
       + "Score - Amoxicillin"
       + "Score - Metronidazole"
       + "Score - Ciprofloxacin"
       + "Score - Paracetamol"
       + "Score - Non-steroidal anti-inflammatory drugs "
       + "Score - Opioid pain medication"
       + "Score - Local anaesthetic"
       + "Score - IV Saline Solution"
       AS "Total - Essential medicines"
FROM sums