MySQL joining two tables and counting based on conditions table 1 > cats 

cat1 
cat2 


table 2 > sites 

site1 &gt; lang1 &gt; cat2 
site2 &gt; lang2 &gt; cat1
site3 &gt; lang3 &gt; cat1
site4 &gt; lang1 &gt; cat1
site5 &gt; lang2 &gt; cat1
site6 &gt; lang3 &gt; cat2


the query should do this : 

get cats and for every cat go to sites and count sites that belongs to that cat with lang1 , lang2 , lang3


I want output to be like : 

cat1 &gt; lang1 : 41 , lang2 : 11 , lang3 : 24

cat2 &gt; lang1 : 17 , lang2 : 06 , lang3 : 74


thanks in advance
I just found the answer and wanted to share it:

SELECT 
  dir_cats.*, 
  SUM(CASE WHEN dir_sites.site_lang = "ar" THEN 1 ELSE 0 END) AS ar_count, 
  SUM(CASE WHEN dir_sites.site_lang = "en" THEN 1 ELSE 0 END) AS en_count,
  SUM(CASE WHEN dir_sites.site_lang = "fr" THEN 1 ELSE 0 END) AS fr_count 
FROM (dir_cats) 
  LEFT JOIN dir_sites 
    ON site_cat_id = cat_id 
GROUP BY dir_sites.site_cat_id