CODEWITHSUNDEEP
sqlsql-server
Red Devil
Red Devil

Reputation: 2393

Calculate count after recursive CTE in ms sql

With the help of recursive CTE I got my results as below

Group_ID    Parent_ID    AliasName    
3           2            DEFAULT    
4           2            TEMPLATE_WIN    
7           2            @@APPLY_TEMPLATE@@    
8           2            WIN_TEST    
9           2            WIN    
12          2            CONNIMPORT    
13          2            INHERITANCE1    
14          2            *_ENGLISH_*    
10          9            WIN_LEN    
11          9            WIN_1    
5           4            TEMP1_WIN    
6           4            TEMP2_WIN  
15          3            TEST

Now I want to count number of child dependent on their parents like WIN_LEN and WIN_1 comes under WIN

so My expected output look like:

Group_ID    Parent_ID    AliasName            Count        
3           2            DEFAULT                1    
4           2            TEMPLATE_WIN           2    
7           2            @@APPLY_TEMPLATE@@     0        
8           2            WIN_TEST               0    
9           2            WIN                    2    
12          2            CONNIMPORT             0    
13          2            INHERITANCE1           0    
14          2            *_ENGLISH_*            0    
10          9            WIN_LEN                0        
11          9            WIN_1                  0    
5           4            TEMP1_WIN              0    
6           4            TEMP2_WIN              0    
15          3            TEST                   0

How to achieve the count part:

My recursive CTE query is :

;WITH cte AS(
        SELECT  Group_ID,Parent_ID,AliasName
        FROM    clientinfo
        WHERE   Parent_ID = 2

        UNION ALL

        SELECT  t.Group_ID,t.Parent_ID,t.AliasName
        FROM    clientinfo t INNER JOIN
                cte r ON t.Parent_ID = r.Group_ID
)

select * from cte

Upvotes: 0

Views: 187

Answers (2)

Thangadurai.B
Thangadurai.B

Reputation: 561

Try this,

      SELECT a.Group_ID, a.Parent_ID, a.AliasName,SUM(IIF(b.Group_ID IS NOT NULL,1,0)) AS cnt
      FROM CTE a
      LEFT JOIN CTE b on a.Group_ID=b.Parent_ID
      group by a.Group_ID, a.Parent_ID, a.AliasName

Upvotes: 1

Thom A
Thom A

Reputation: 95561

Seems like you could achieve this with a subquery. I've personally performed this in the FROM using APPLY, but you could do this in the SELECT. Note that 'DEFAULT' has a value of 0 for [Count], as 'TEST' is not in your sample data:

--; is a statement TERMINATOR not "beginningator"
WITH VTE AS
    (SELECT V.Group_ID,
            V.Parent_ID,
            RTRIM(V.AliasName) AS AliasName
     FROM (VALUES (3, 2, 'DEFAULT           '),
                  (4, 2, 'TEMPLATE_WIN      '),
                  (7, 2, '@@APPLY_TEMPLATE@@'),
                  (8, 2, 'WIN_TEST          '),
                  (9, 2, 'WIN               '),
                  (12,2, 'CONNIMPORT        '),
                  (13,2, 'INHERITANCE1      '),
                  (14,2, '*_ENGLISH_*       '),
                  (10,9, 'WIN_LEN           '),
                  (11,9, 'WIN_1             '),
                  (5, 4, 'TEMP1_WIN         '),
                  (6, 4, 'TEMP2_WIN         '),
                  (15,3, 'TEST              ')) V (Group_ID, Parent_ID, AliasName) ),
rCTE AS
    (SELECT Group_ID,
            Parent_ID,
            AliasName
     FROM VTE
     WHERE Parent_ID = 2
     UNION ALL
     SELECT t.Group_ID,
            t.Parent_ID,
            t.AliasName
     FROM VTE t
          INNER JOIN rCTE r ON t.Parent_ID = r.Group_ID)
SELECT r.Group_ID,
       r.Parent_ID,
       r.AliasName,
       C.[Count]
FROM rCTE r
     CROSS APPLY (SELECT COUNT(*) AS [Count]
                  FROM VTE V
                  WHERE V.Parent_ID = r.Group_ID) C;

'TEST' has been added to the sample data.

Upvotes: 1

Related Questions