CREATE TABLE GOOD ( --товары
      ID Integer NOT NULL, --первичный ключ
      LEFTBOUND Integer NOT NULL,
      RIGHTBOUND Integer NOT NULL,
      PARENT Integer,--сылка на группу владельца, для первого уровня NULL
      NAME Varchar( 50 ) NOT NULL, --наименование группы или товара
      ISGROUP Integer NOT NULL --признак группы: 0,1
);

C.LEFTBOUND<B.LEFTBOUND<A.LEFTBOUND=A.RIGHTBOUND<=B.RIGHTBOUND<=C.RIGHTBOUND

Группа 1
 Группа 1.1
  Товар 1.1.1
  Товар 1.1.2
 Товар 1.2
 Товар 1.3
Группа 2
 Группа 2.1
 Группа 2.2

select g.ID, g.Name, g.ISGROUP, count(g2.ID) as glevel
from GOOD g
left join GOOD g2 on (g.LEFTBOUND between g2.LEFTBOUND and g2.RIGHTBOUND)
group by g.ID, g.Name, g.ISGROUP, g.LEFTBOUND
order by g.LEFTBOUND

CREATE TABLE SALE (--продажи
       ID Integer NOT NULL, 
       GOOD Integer NOT NULL, --ссылка на исходный справочник
       SUMMA Numeric( 15 , 3 ) NOT NULL --сумма продажи
);

Молочные продукты 150
  Молоко          100
    Молоко 3%      70
    Молоко 6%      30
  Кефир 3%         50

SELECT A.Х
FROM T as A
LEFT JOIN T as B on ( 1 = 1 )
GROUP BY A.X
ORDER BY SUM(r(A,B)) DESC --запись формальная естественно

SELECT A.Х
FROM T as A
LEFT JOIN T as B on (r(A,B)= 1 )
GROUP BY A.X
ORDER BY COUNT(B.Х) DESC

SELECT 
T.ID1 ID ,T.NAME1 NAME, COUNT(DISTINCT GLEV.ID) LEV
FROM
(SELECT G1.ID ID1, G2.ID ID2, MAX(MP.LEFTBOUND) MPL, MIN(MP.RIGHTBOUND) MPR, G1.LEFTBOUND GL1, G2.LEFTBOUND GL2, G1.NAME NAME1 
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD MP ON ((G1.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND) AND (G2.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND))
GROUP BY G1.ID, G2.ID, G1.LEFTBOUND, G2.LEFTBOUND, G1.NAME) T 
LEFT JOIN GOOD MP0 ON (MP0.LEFTBOUND = T.MPL)
LEFT JOIN GOOD GI1 ON ((GI1.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI1.PARENT IS NULL))) AND (T.GL1 BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON ((GI2.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI2.PARENT IS NULL))) AND (T.GL2 BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
LEFT JOIN GOOD GLEV ON (T.GL1 BETWEEN GLEV.LEFTBOUND AND GLEV.RIGHTBOUND) 
GROUP BY T.ID1, T.NAME1
ORDER BY
      SUM(
      CASE
        WHEN T.ID1=MP0.ID THEN  1 
        WHEN T.ID2=MP0.ID THEN  0 
        WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN  1 
        WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN  0         
        WHEN (GI1.NAME<GI2.NAME) THEN  1         
        ELSE  0 
      END) DESC

ORDER BY
     COUNT( DISTINCT
     CASE
       WHEN T.ID1=MP0.ID THEN ID2
       WHEN T.ID2=MP0.ID THEN NULL
       WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN ID2
       WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN NULL        
       WHEN (GI1.NAME<GI2.NAME) THEN ID2        
       ELSE NULL
     END)