Count of items by depth with a nested set model

Question

I'm using the nested set model for my database (from here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), it works perfectly, but...

From the "Depth of a Sub-Tree" request, I want an other result.

Initial result:

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

And I want the count of items by depth, example from the initial data:

+----------------------+-------+
| depth                | count |
+----------------------+-------+
|                    1 |     3 |
|                    2 |     1 |
+----------------------+-------+

I tried to work with group by and/or count(), but it doesn't work...

Thanks for your help!

SELECT depth,COUNT(*) FROM (your query here) x GROUP BY depth — Strawberry, Jan 27 '15 at 22:43

score 0 · Accepted Answer · answered Jan 27 '15 at 23:10

0

Here the answer from the Strawberry's comment:

SELECT depth, COUNT(*) FROM (your query here) x GROUP BY depth

answered Jan 27 '15 at 23:10

Surfoo

88
1
12

Count of items by depth with a nested set model

1 Answers1