SELECT 
        IF(c1.parentId = '44', c1.id, IF(c2.parentId = '44', c2.id, IF(c3.parentId = '44', c3.id, IF(c4.parentId = '44', c4.id, NULL )))) AS id,
        IF(c1.parentId = '44', cl1.desc, IF(c2.parentId = '44', cl2.desc, IF(c3.parentId = '44', cl3.desc, IF(c4.parentId = '44', cl4.desc, NULL )))) AS `desc`,        
        COUNT(IF(c1.parentId = '44', c1.id, IF(c2.parentId = '44', c2.id, IF(c3.parentId = '44', c3.id, IF(c4.parentId = '44', c4.id, NULL ))))) AS cnt
       FROM  products_by_category as pbc 
            INNER JOIN products as p ON pbc.productId=p.productId 
            INNER JOIN products_lang as pl ON p.productId=pl.id AND pl.lang='1'   
            LEFT JOIN categories AS c1 ON pbc.type = c1.id 
            LEFT JOIN categories_lang AS cl1 ON c1.id = cl1.id AND cl1.lang='1'  
            LEFT JOIN categories AS c2 ON c1.parentId = c2.id 
            LEFT JOIN categories_lang AS cl2 ON c2.id = cl2.id AND cl2.lang='1' 
            LEFT JOIN categories AS c3 ON c2.parentId = c3.id 
            LEFT JOIN categories_lang AS cl3 ON c3.id = cl3.id AND cl3.lang='1' 
            LEFT JOIN categories AS c4 ON c3.parentId = c4.id   
            LEFT JOIN categories_lang AS cl4 ON c4.id = cl4.id AND cl4.lang='1'                       
           WHERE 1 AND (pbc.type='44' OR c1.parentId='44' OR c2.parentId='44' OR c3.parentId='44' OR c4.parentId='44')  
          AND p.isInvis='0' AND p.isInArchive='0' 
          GROUP BY IF(c1.parentId = '44', c1.id, IF(c2.parentId = '44', c2.id, IF(c3.parentId = '44', c3.id, IF(c4.parentId = '44', c4.id, NULL ))))  
          HAVING id IS NOT NULL 
          ORDER BY IF(c1.parentId = '44', c1.poradi, IF(c2.parentId = '44', c2.poradi, IF(c3.parentId = '44', c3.poradi, IF(c4.parentId = '44', c4.poradi, NULL ))))        
          

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bescnbe1_shop.c1.parentId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by