>To only get first level subcategories count:
>
>

>SELECT Category.Numero, Category.Anglais, ISNULL(SubC.Compte,0) as Compte
> FROM Category
>OUTER APPLY (select count(*) as Compte from Category AS Category2 
>where Category.Numero=Category2.NoCategory) SubC
> WHERE Category.NoClient=78 AND Category.NoCategory=0
>

This ones avoid the usage of repetitive MIN(). It is much better.