SQL SUM (DISTINCT ...) in the result set

Question

Below are two tables TBL1 and TBL2

TBL1
ID1    Item    Val1     
100    Shirt    10      
101    Shirt    15      
102    Shirt    10      
100    Shirt    10      
100    Shirt    10      
103    Pants    14      

TBL2        

ID2    ID1    Val2
901    100    24
902    101    15
903    102    31
904    100    45
905    100    62
906    103    17

Now I am executing the following query in these two tables:

SELECT TBL1.Item AS Item,
       Count(DISTINCT TBL1.ID1) AS Cpt,
       SUM (TBL1.Val1) AS Tot1,
       SUM (TBL2.Val2) AS Tot2
FROM TBL1, TBL2
WHERE TBL1.ID1 = TBL2.ID2
GROUP BY TBL1.Item

The problem is that the SUM(TBL1.Val1)redundant value will not be taken into account, that is, the same TBL1.ID1. If I use SUM(DISTINCT TBL1.ID1), some information will not be taken into account. Here for ID1 = 102 this value Val1 = 10 will not be used in SUM (DISTINCT TBL1.ID1).

Do you think I can rewrite this request so that SUM will work only for a separate ID1? Thus, the result is as follows:

Item    Cpt    Tot1    Tot2
Shirt    5      35      177
Pants    1      14      17

Answer 1

This is what I came with:

DECLARE @TBL1 TABLE (
  ID1 INT
  , Item VARCHAR(10)
  , Val1 INT
  );

DECLARE @TBL2 TABLE (
  ID2 INT
  , ID1 INT
  , Val2 INT
  );

INSERT INTO @TBL1 (ID1, Item, Val1)
VALUES (100, 'Shirt', 10)
  , (101, 'Shirt', 15)
  , (102, 'Shirt', 10)
  , (100, 'Shirt', 10)
  , (100, 'Shirt', 10)
  , (103, 'Pants', 14);

INSERT INTO @TBL2 (ID2, ID1, Val2)
VALUES (901, 100, 24)
  , (902, 101, 15)
  , (903, 102, 31)
  , (904, 100, 45)
  , (905, 100, 62)
  , (906, 103, 17);

;WITH Items (ID1, Item, Val1)
AS (
  SELECT DISTINCT ID1, Item, Val1
  FROM @TBL1
  )
, Items2 (Item, Tot1) 
  AS (
  SELECT Item, SUM(Val1)
  FROM Items
  GROUP BY Item
  )
, TBL2 (ID1, Tot2)
  AS (
  SELECT ID1, SUM(Val2)
  FROM @TBL2
  GROUP BY ID1
  )
SELECT T.Item
  , COUNT(T1.ID1) AS Cpt
  , T.Tot1
  , SUM(DISTINCT T2.Tot2)
FROM Items2 AS T
INNER JOIN @TBL1 AS T1
  ON T1.Item = T.Item
INNER JOIN TBL2 AS T2
  ON T2.ID1 = T1.ID1
GROUP BY T.Item, T.Tot1;

It is not so elegant, but it does the job. Result:

╔═══════╦═════╦══════╦══════╗
║ Item  ║ Cpt ║ Tot1 ║ Tot2 ║
╠═══════╬═════╬══════╬══════╣
║ Pants ║   1 ║   14 ║   17 ║
║ Shirt ║   5 ║   35 ║  177 ║
╚═══════╩═════╩══════╩══════╝

data.stackexchange.com

, CTE:

SELECT T.Item
  , COUNT(T1.ID1) AS Cpt
  , T.Tot1
  , SUM(DISTINCT T2.Tot2) AS Tot2
FROM (
  SELECT Item, SUM(Val1) AS Tot1
  FROM (SELECT DISTINCT ID1, Item, Val1 FROM @TBL1) AS X
  GROUP BY X.Item
  ) AS T
INNER JOIN @TBL1 AS T1
  ON T1.Item = T.Item
INNER JOIN (
  SELECT ID1, SUM(Val2) AS Tot2
  FROM @TBL2
  GROUP BY ID1
  ) AS T2
  ON T2.ID1 = T1.ID1
GROUP BY T.Item, T.Tot1;

data.stackexchange.com

. 2

, - , SUM(DISTINCT T2.Tot2) AS Tot2. , .

SELECT T1.Item
  , T3.Cpt
  , T1.Tot1
  , T2.Tot2
FROM (
  SELECT Item, SUM(Val1) AS Tot1
  FROM (SELECT DISTINCT ID1, Item, Val1 FROM @TBL1) AS X
  GROUP BY X.Item
  ) AS T1
INNER JOIN (
  SELECT T1.Item, SUM(Val2) AS Tot2
  FROM (SELECT DISTINCT ID1, Item, Val1 FROM @TBL1) AS T1
  INNER JOIN @TBL2 AS T2
    ON T2.ID1 = T1.ID1
  GROUP BY T1.Item
  ) AS T2
  ON T2.Item = T1.Item
INNER JOIN (
  SELECT Item, COUNT(*) AS Cpt
  FROM @TBL1
  GROUP BY Item
  ) AS T3
  ON T3.Item = T1.Item;