[SOLVED] Count of items by depth with a nested set model

Count of items by depth with a nested set model

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!

Solution

Here the answer from the Strawberry's comment:

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