CTE or not for counting tree id’s referenced in secondary table

I have a simple tree table in which I reference types, sub-types (no level limit), brands and models for a simple inventory application.

data structure is as shown below: (nodeIsModel is a boolean field, used for calculations)

ID    parentID    label               nodeIsModel
 1     -1          Root                   0  
 2     1           IT                     0
 3     2           Desktops               0
 4     3           Hewlett-Packard        0 
 5     4           HP Z240                1
 6     4           H97M-PLUS              1
 7     2           Laptops                0
 8     7           DELL                   0
 9     8           G3 3579                1

and so on.

This table (displayed in a DBTreeView under Delphi) gives me something like:

ID     Label
1      Root
2        |_IT
3        |__Desktops
4        |___Hewlett-Packard
5        |____HP Z240
6        |____H97M-PLUS
7        |__Laptops
8        |___DELL
9        |____G3 3579

This table is the reference table in which the user will pick and assign to an equipment, a set of type, sub-types, brands and models.

Equipment is saved in another table (data_items) and linked to the previous table by the id of the selected model (lowest level of tree structure).

As an example, my desktop computer would be saved as:

itemID    itemLabel         typeID
  1       RSI-HP-DESK-01      5

because it’s a HP Z240 workstation and it is referenced as ID 5 in the tree table.

Starting from this typeID, I have a CTE query that climbs up the tree to find all parents from child ID, this works no problem.

My question is as follows:

I would like, when displaying the DBTreeView, to had a column displaying:

• the item count for all models

but also

• the sum of models for each brand (counting the models)
• the sum of models for each sub-types
• the sum of models for each type…

and so on…

As an example, if we had 2 HP Z240 workstation and 1 DELL G3 laptop in database, the TreeView would look like:

ID     Label                    NB
1      Root
2        |_IT                   3
3        |__Desktops            2
4        |___Hewlett-Packard    2
5        |____HP Z240           1
6        |____H97M-PLUS         1
7        |__Laptops             1
8        |___DELL               1
9        |____G3 3579           1

Based on the “nodeIsModel” boolean field in the tree table, I have tried queries, some with CTE others without, but I am getting confused on the JOIN and sub-queries, also getting the error message about:

aggregate queries not being allowed in CTE

Any advice would be very much appreciated.

---

As per request of Dale K, here is the recursive query giving me all the parents starting from a child ID :

This query works. I am looking for a new query that would join the tree table with the item table giving a count on models assigned to items at each level of the tree

WITH CTE AS (
SELECT
    ID,
    parentID,
    label,
    CAST (nodeLevel AS INTEGER) AS LEVEL
FROM
    dico_TBM
WHERE
    ID = :lookupID
UNION ALL
    SELECT
        r.ID,
        t.parentID,
        t.label,
        LEVEL - 1
    FROM
        dico_TBM t
    INNER JOIN CTE r ON t.ID = r.parentID
) SELECT DISTINCT
    r.ID,
    r.parentID,
    r.label,
    LEVEL
FROM
    CTE r
WHERE
    LEVEL > 0
ORDER BY
    LEVEL

(nodeLevel being an integer giving the level of the node in the tree I omitted to mention in my question, thinking it was of no interest, and :lookupID being the ID of the starting child node I pass as a parameter to my query)

Answer

Here’s one approach which may help you solve the problem. Below is my similar setup

And the following query gives us the count of resources on each level in the resource tree.

;with CTE1 AS (
  SELECT r.id, r.label, r.parentID, count(*) AS ItemCount 
    FROM resourcetree r 
    JOIN items i ON (r.id = i.resourceID) 
   GROUP BY r.id, r.label, r.parentID
), 
CTE2 AS (
  SELECT r.id, r.label, r.parentID, SUM(cte1.itemCount) AS ItemCount, 0 AS sumFlag 
    FROM resourceTree r 
    JOIN CTE1 ON (r.id = cte1.parentID) 
   GROUP BY r.id, r.label, r.parentID
  UNION ALL
  SELECT r.id, r.label, r.parentID, cte2.ItemCount AS ItemCount, 1 AS sumFlag 
    FROM resourceTree r 
    JOIN cte2 ON (r.id = cte2.parentID)
)
SELECT r.id, 
       r.label, 
       COALESCE(c2_1.ItemCount, c2_0.ItemCount, cte1.ItemCount, 0) AS ItemCount 
  FROM resourceTree r 
  LEFT JOIN cte1 ON r.id = cte1.id 
  LEFT JOIN cte2 c2_0 ON (r.id = c2_0.id AND c2_0.sumFlag = 0)
  LEFT JOIN (SELECT id, label, SUM(ItemCount) AS ItemCount 
               FROM CTE2 
              WHERE sumFlag = 1 
              GROUP BY id, label) c2_1 ON r.id = c2_1.id

Below is the result I got. Hope it helps. Thanks.

PS: I didn’t have to use the ‘NodeIsModel’ flag