Get Field Hierachy

Question

I have the following tables and I want to get the quantity of users by country:

+--------+------+:
| user   | zone |
+--------+------+
| Paul   | 7    |
+--------+------+
| John   | 5    |
+--------+------+
| Peter  | 6    |
+--------+------+
| Frank  | 5    |
+--------+------+
| Silvia | 2    |
+--------+------+
| Carl   | 4    |
+--------+------+
| Mark   | 3    |
+--------+------+

Regions

+---------+-----------------+----------+--+
| zone_id | zone_name       | idUpzone |  |
+---------+-----------------+----------+--+
| 1       | Global          | null     |  |
+---------+-----------------+----------+--+
| 2       | US              | 1        |  |
+---------+-----------------+----------+--+
| 3       | Florida         | 2        |  |
+---------+-----------------+----------+--+
| 4       | Orlando         | 3        |  |
+---------+-----------------+----------+--+
| 5       | China           | 1        |  |
+---------+-----------------+----------+--+
| 6       | Orlando Sector  | 4        |  |
+---------+-----------------+----------+--+
| 7       | Beijing         | 5        |  |
+---------+-----------------+----------+--+

so I get something like this

+---------+-----+
| Country | QTY |
+---------+-----+
| US      | 4   |
+---------+-----+
| China   | 3   |
+---------+-----+

https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — dejoma, Mar 05 '20 at 19:38

Gordon Linoff · Accepted Answer · 2020-03-05T22:49:07.303

1

Use a recursive CTE to get the highest level and then join:

with cte as (
      select zone_id, zone_id as top_zone_id, zone_name as top_zone_name, 1 as lev
      from regions
      where parent_zone_id = 1
      union all
      select r.zone_id, cte.top_zone_id, top_zone_name, lev + 1
      from cte join
           regions r
           on r.idUpzone = cte.zone_id
    )
select cte.top_zone_name, count(*)
from users u join
     cte 
     on u.zone = cte.zone_id
group by cte.top_zone_name;

edited Mar 05 '20 at 22:49

answered Mar 05 '20 at 19:38

Gordon Linoff

1,242,037
58
646
786

Replace `parent_zone_id` by `idUpzone` and it's good. And... you forgot `top_zone_name` in the second part of the CTE. – The Impaler Mar 05 '20 at 19:47

score 0 · Answer 2 · answered Mar 05 '20 at 19:52

0

Try this out:

SELECT 
    r.zone_name AS Contry, COUNT(*) QTY 
FROM (
    SELECT * FROM users u
    INNER JOIN regions r ON u.zone = r.zone_id
) a
GROUP BY r.zone_name

answered Mar 05 '20 at 19:52

Nayanish Damania

542
5
13

This won't work since it's a multi-level hierarchy. – The Impaler Mar 05 '20 at 20:24

Get Field Hierachy

2 Answers2