stored procedures - Sum at each level of a tree view in sql

Question

I have a table that looks like this

CREATE TABLE [dbo].[CNF_StatisticalGroup]
(
    [StatisticalGroupId] [int] IDENTITY(1,1) NOT NULL,
    [StatisticalGroupCode] [varchar](10) NOT NULL,
    [StatisticalGroupDescription] [nvarchar](100) NULL,
    [ParentId] [int] NULL
) 

With ParentID being self referencing.

My data for instance my look like this:

18  Consumer    Consumer Direct             NULL
19  Win         Walkin                      18
20  OL          Online                      18
21  100         100/F                       19
22  ME          Ticket Centre               19

As you can see that Ticket Centre belongs under Walkin and Walkin belongs under Consumer Direct.

I need to be able to count the number of items under a level and add it to the above level and so on.

For instance.

• Ticket Centre has a count of 2
• Walkin has an individual count of 3
• Consumer Direct has an individual count of 1

So it looks like this

Consumer Direct    1
Walkin             3
Ticket Center      2

What I need is to add the ticket center 2 to the walkin of 3 so that Walking has a count of 5 so something like this

Description        Individual Count      Cummulative Count
-----------------------------------------------------------
Consumer Direct    1                     6
Walkin             3                     5
Ticket Center      2                     2

Can someone please help me create this in a SQL statement or a view or a stored procedure?

Answer 1

Assuming you are using MS SQL (or a DBMS with CTE support), the below query should help.

WITH cte 
     AS (SELECT csg.statisticalgroupid, 
                statisticalgroupdescription, 
                1 AS IndCount, 
                parentid 
         FROM   cnf_statisticalgroup csg 
                INNER JOIN test_data td 
                        ON csg.statisticalgroupid = td.statisticalgroupid 
         UNION ALL 
         SELECT cte.parentid, 
                csg.statisticalgroupdescription, 
                0 AS IndCount, 
                csg.parentid 
         FROM   cnf_statisticalgroup csg 
                INNER JOIN cte 
                        ON cte.parentid = csg.statisticalgroupid 
         WHERE  cte.parentid IS NOT NULL) 
SELECT statisticalgroupdescription, 
       Sum(indcount) AS 'Individual Count', 
       Count(*)      AS 'Cumulative Count' 
FROM   cte 
GROUP  BY statisticalgroupdescription 

Refer: http://sqlfiddle.com/#!18/c5576/9

Also, you have not mentioned which table contains the count for each consumer. For the query, I have used test_data table to contain the individual records