MySql - List with Count for an Specific Field

Question

How can i count while listing for an specific field? For my example (on the image), the intended result for COUNT_ROWS should be:

/App/RaspLot - 2;
/App/RaspLot - 2;
/Home/ePortal_Options - 2;
/Home/ePortal_Options - 2;
/Home/ePortal_Logout - 1;

Current Result on Image Below:

I'm Trying:

SELECT T.`MAIN_PATH`, A.`SUB_PATH`, T.`COUNT_ROWS`
FROM `SIDENAV` A 
INNER JOIN (
    SELECT `MAIN_PATH`,  COUNT(DISTINCT `MAIN_PATH`) AS `COUNT_ROWS` 
    FROM `SIDENAV` 
    GROUP BY `MAIN_PATH`
) T ON A.`MAIN_PATH` = T.`MAIN_PATH` 
ORDER BY `ORDER_1`, `MAIN_NAME`, `ORDER_2` ASC 

Answer 1

It really looks like you just want aggregation:

select main_path, count(*) as count_rows
from sidenav
group by main_path
order by count_rows desc, main_path

If you want the count on each row, you can use window functions:

select s.*, count(*) over(partition by main_path) as count_rows
from sidenav s

This requires MySQL 8.0. In earlier versions, you can perform the window count with a subquery:

select s.*,
    (select count(*) from sidenav s1 where s1.main_path = s.main_path) as count_rows
from sidenav s