SELECT t1.IDprovince AS Num,
t1.province AS province,
t2.Nbre_Pompe,
t2.Pompe_Fonctionnelle,
t2.Pompe_Non_Fonctionnelle,
t3.Nbre_artisan,
t4.Nbre_Forage

FROM province AS t1

LEFT OUTER JOIN ( SELECT province,
COUNT(IDpompe) AS Nbre_Pompe,
SUM(CASE WHEN etat_pompe LIKE 'Non fonctionnelle' THEN 0 ELSE 1 END) AS Pompe_Fonctionnelle,
SUM(CASE WHEN etat_pompe LIKE 'Fonctionnelle' THEN 0 ELSE 1 END) AS Pompe_Non_Fonctionnelle
FROM pompe
GROUP BY province
) AS t2
ON t2.province = t1.province

LEFT OUTER JOIN ( SELECT province,
COUNT(IDartisan) AS Nbre_artisan
FROM artisan
GROUP BY province
) AS t3
ON t3.province = t1.province

LEFT OUTER JOIN ( SELECT province,
COUNT(IDforage) AS Nbre_Forage
FROM forage
GROUP BY province
) AS t4
ON t4.province = t1.province