MySQL: Counting results from multiple LEFT JOINS

I have three tables that describe data where there are some main things. Each thing can have several categories below it, and each category can have multiple segments.

main

categories

segments

I'm augmenting the main table with a column that shows the number of categories below each main thing, as well as the total number of segments in all categories belonging to it.

I tried with the following SQL statement:

SELECT
    m.main_id, m.main_name, count(c.cat_id) as n_cats, count(s.seg_id) as n_segs
FROM main
    LEFT JOIN categories c ON c.main_id = m.main_id
    LEFT JOIN segments s ON s.cat_id = c.cat_id
GROUP BY m.main_id

But it yields the same values for n_cats and n_segs, instead of different ones as desired:

I executed you SQL with same data set,

SELECT
    m.main_id, m.main_name, COUNT(c.cat_id) AS n_cats, COUNT(s.seg_id) AS n_segs
FROM main m
    LEFT JOIN categories c ON c.main_id = m.main_id
    LEFT JOIN segments s ON s.cat_id = c.cat_id
GROUP BY m.main_id

I am getting this result, the only difference is I use m as an alias FROM main m

It looks like you need this SELECT clause using COUNT(DISTINCT col).

SELECT m.main_id, m.main_name, 
       count(DISTINCT c.cat_id) as n_cats, 
       count(DISTINCT s.seg_id) as n_segs

This will undo the combinatorial explosion caused by your JOINs.