How to sum the distinguishing values ​​of repeating fields

Question

I am new here and hope that I can find some help here.

I have the following problem: Fiddle SQL Demo

CREATE TABLE TestData
    (
      [Acct] INT ,
      [Desc] VARCHAR(100) ,
      [OU01] VARCHAR(100) ,
      [OU02] VARCHAR(100) ,
      DEPTHS INT
    );

CREATE TABLE TestDepends
    (
      [Acct] INT ,
      [SecondKey] NUMERIC(9, 0) ,
      [Name] VARCHAR(100) ,
      [COST] NUMERIC(9, 0) ,
      [used] TINYINT ,
      [OU01] VARCHAR(100) ,
      [OU02] VARCHAR(100)
    );



INSERT  INTO TestData
VALUES  ( 1, 'Feld 1', 'OU01', '', 1 );
INSERT  INTO TestData
VALUES  ( 1, 'Feld 1', 'OU01', 'OU02', 2 );
INSERT  INTO TestData
VALUES  ( 1, 'Feld 1', 'OU01', 'OU03', 2 );
INSERT  INTO TestData
VALUES  ( 1, 'Feld 1', 'OU02', '', 1 );


INSERT  INTO TestDepends
VALUES  ( 1, 21, 'Name 1', 120, 0, 'OU01', 'OU02' );
INSERT  INTO TestDepends
VALUES  ( 1, 22, 'Name 2', 50, 0, 'OU01', '' );
INSERT  INTO TestDepends
VALUES  ( 1, 21, 'Name 1', 120, 1, 'OU01', 'OU02' );
INSERT  INTO TestDepends
VALUES  ( 1, 23, 'Name 3', 111, 0, 'OU01', 'OU03' );
INSERT  INTO TestDepends
VALUES  ( 1, 24, 'Name 4', 99, 0, 'OU02', '' );
INSERT  INTO TestDepends
VALUES  ( 1, 25, 'Name 5', 120, 0, 'OU01', 'OU02' );

SELECT  TD.Acct ,
        COUNT(DISTINCT TDS.SecondKey) AS Found ,
        COUNT(DISTINCT CASE WHEN TDS.Used = 1 THEN TDS.SecondKey
                       END) AS Used ,
        TD.OU01 ,
        TD.OU02
FROM    TestData TD
        INNER JOIN TestDepends TDS ON TDS.Acct = TD.Acct
                                      AND ( TD.OU01 = ''
                                            OR ISNULL(TD.OU01, '') = ISNULL(TDS.OU01, '')
                                          )
                                      AND ( TD.OU02 = ''
                                            OR ISNULL(TD.OU02, '') = ISNULL(TDS.OU02, '')
                                          )
WHERE   TD.depths = 2
GROUP BY TD.Acct ,
        TD.OU01 ,
        TD.OU02

As you can see in the first Select I count various Data (SecondKey), and this works fine.

Now I want to summarize all the costs of all the individual data

  SELECT  TD.Acct ,
            SUM(TDS.Cost) AS Cost ,
            SUM(TDS.Cost) AS Cost ,
            TD.OU01 ,
            TD.OU02
    FROM    TestData TD
            INNER JOIN TestDepends TDS ON TDS.Acct = TD.Acct
                                          AND ( TD.OU01 = ''
                                                OR ISNULL(TD.OU01, '') = ISNULL(TDS.OU01, '')
                                              )
                                          AND ( TD.OU02 = ''
                                                OR ISNULL(TD.OU02, '') = ISNULL(TDS.OU02, '')
                                              )
    WHERE   TD.depths = 2
    GROUP BY TD.Acct ,
            TD.OU01 ,
            TD.OU02

As you can see, it sums up “duplicates” (not all rows are duplicates, as well as SecondKeys rows). Is it possible to summarize all the costs of only individual SecondKeys?

Thanks in advance.

Answer 1

, TestDepends, GROUP BY, :

....
FROM    TestData TD
        INNER JOIN 
           ( 
            SELECT Acct,
                   SecondKey,   
                   Name,
                   cost,
                   MAX(used) as used,
                   OU01,
                   OU02
             FROM
             TestDepends 
             GROUP BY Acct,SecondKey,Name,cost, OU01, OU02
           )  
         TDS ON ....

- SQL Fiddle

Answer 2

: , Distinct, USED t1

  SELECT  TD.Acct ,
            SUM(t1.Cost) AS Cost ,           
            TD.OU01 ,
            TD.OU02
    FROM    TestData TD
            INNER JOIN
              (select distinct Acct, Secondkey, Name, Cost, ou01, ou02 
               from TestDepends) t1 ON t1.Acct = TD.Acct
                                          AND ( TD.OU01 = ''
                                                OR ISNULL(TD.OU01, '') = ISNULL(t1.OU01, '')
                                              )
                                          AND ( TD.OU02 = ''
                                                OR ISNULL(TD.OU02, '') = ISNULL(t1.OU02, '')
                                              )
    WHERE   TD.depths = 2
    GROUP BY TD.Acct ,
            TD.OU01 ,
            TD.OU02

demo: http://sqlfiddle.com/#!3/76470/23