Untitled

SELECT 
    der.id,
    der.name,
    der.parent_id,
    der.sort,
    COUNT(sub_categories.cat_id) AS sub_count
FROM (
    (
        SELECT 
            cat_id AS id, 
            category_name AS name,
            parent_id,
            category_name AS sort
        FROM news_categories
        WHERE parent_id IS NULL
    ) 
    UNION 
    (
        SELECT 
            c.cat_id AS id, 
            c.category_name AS name,
            c.parent_id,
            CONCAT(p.category_name, "_", c.category_name) AS sort
        FROM news_categories AS c
        INNER JOIN news_categories AS p ON c.parent_id = p.cat_id
    )
) AS der
LEFT JOIN news_categories AS sub_categories ON sub_categories.parent_id = der.id
GROUP BY der.id, der.name, der.parent_id, der.sort
ORDER BY der.sort;