title: subqueries with different groupings in db2 not returning division results correctly
body: <p>I absolutely can't figure out what I'm doing wrong.</p>
  
  <p>My data here is close but my V2 and V3 subqueries are not returning the correct data and I think it's based on the fact that I'm trying to divide by groupings on a SUM and it feels like it's using the same strCount number across the board.</p>
  
  <p>The query:</p>
  
  <pre><code>WITH T (employee, manager, GROUP, detail1, detail2, strCount, QUANT, LAST_SHIP_DATE) AS (
  VALUES  (21177, 165, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (19445, 182, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (20949, 340, 4867, 2878, 29, 1, 2, CURRENT DATE - 10 DAY),
          (21347, 353, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (23068, 353, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (17195, 353, 4867, 2878, 29, 1, 6, CURRENT DATE - 10 DAY),
          (23040, 353, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (5620,  380, 4867, 2878, 29, 1, 4, CURRENT DATE - 10 DAY),
          (23009, 380, 4867, 2878, 29, 1, 3, CURRENT DATE - 10 DAY),
          (13529, 390, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (22706, 394, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (17049, 394, 4867, 2878, 29, 1, 4, CURRENT DATE - 10 DAY),
          (17879, 397, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (21320, 445, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY),
          (21231, 457, 4867, 2878, 29, 1, 1, CURRENT DATE - 10 DAY)
  )
  SELECT T.employee, T.manager, T.GROUP, T.detail1, T.detail2, T.strCount, T.QUANT
  , COALESCE(T1.V1, 0) V1, COALESCE(T2.V2, 0) V2, COALESCE(T3.V3, 0) V3
  FROM T
  LEFT JOIN (
  SELECT employee, GROUP, detail1, detail2, SUM(QUANT)/SUM(strCount)*12 V1
  FROM T
  WHERE last_ship_date &gt; CURRENT_DATE - 30 DAYS
  GROUP BY employee, GROUP, detail1, detail2,strCount
  ) T1 ON T1.employee=T.employee AND T1.GROUP=T.GROUP AND T1.detail1=T.detail1 AND T1.detail2=T.detail2 
  LEFT JOIN (
  SELECT manager, GROUP, detail1, detail2, SUM(QUANT)/SUM(strCount)*12 V2
  FROM T
  WHERE last_ship_date &gt; CURRENT_DATE - 30 DAYS
  GROUP BY manager, GROUP, detail1, detail2,strCount
  ) T2 ON T2.manager=T.manager AND T2.GROUP=T.GROUP AND T2.detail1=T.detail1 AND T2.detail2=T.detail2
  LEFT JOIN (
  SELECT GROUP, detail1, detail2, SUM(QUANT)/SUM(strCount)*12 V3
  FROM T
  WHERE last_ship_date &gt; CURRENT_DATE - 30 DAYS
  GROUP BY GROUP, detail1, detail2,strCount
  ) T3 ON T3.GROUP=T.GROUP AND T3.detail1=T.detail1 AND T3.detail2=T.detail2;
  </code></pre>
  
  <p>This gives me </p>
  
  <pre><code>employee | manager | group | detail1 | detail2 | strCount | quant | V1 | V2 | V3
  ================================================================================
  21177    | 165     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 12
  19445    | 182     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 12
  20949    | 340     | 4867  | 2878    | 29      | 1        | 2     | 24 | 24 | 12
  21347    | 353     | 4867  | 2878    | 29      | 1        | 1     | 12 | 24 | 12
  23068    | 353     | 4867  | 2878    | 29      | 1        | 1     | 12 | 24 | 12
  17195    | 353     | 4867  | 2878    | 29      | 1        | 6     | 72 | 24 | 12
  23040    | 353     | 4867  | 2878    | 29      | 1        | 1     | 12 | 24 | 12
  5620     | 380     | 4867  | 2878    | 29      | 1        | 4     | 48 | 36 | 12
  23009    | 380     | 4867  | 2878    | 29      | 1        | 3     | 36 | 36 | 12
  13529    | 390     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 12
  22706    | 394     | 4867  | 2878    | 29      | 1        | 1     | 12 | 24 | 12
  17049    | 394     | 4867  | 2878    | 29      | 1        | 4     | 48 | 24 | 12
  17879    | 397     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 12
  21320    | 445     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 12
  21231    | 457     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 12
  </code></pre>
  
  <p>But Expecting:</p>
  
  <pre><code>employee | manager | group | detail1 | detail2 | strCount | quant | V1 | V2 | V3
  ==================================================================================
  21177    | 165     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 22.4
  19445    | 182     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 22.4
  20949    | 340     | 4867  | 2878    | 29      | 1        | 2     | 24 | 24 | 22.4
  21347    | 353     | 4867  | 2878    | 29      | 1        | 1     | 12 | 27 | 22.4
  23068    | 353     | 4867  | 2878    | 29      | 1        | 1     | 12 | 27 | 22.4
  17195    | 353     | 4867  | 2878    | 29      | 1        | 6     | 72 | 27 | 22.4
  23040    | 353     | 4867  | 2878    | 29      | 1        | 1     | 12 | 27 | 22.4
  5620     | 380     | 4867  | 2878    | 29      | 1        | 4     | 48 | 42 | 22.4
  23009    | 380     | 4867  | 2878    | 29      | 1        | 3     | 36 | 42 | 22.4
  13529    | 390     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 22.4
  22706    | 394     | 4867  | 2878    | 29      | 1        | 1     | 12 | 30 | 22.4
  17049    | 394     | 4867  | 2878    | 29      | 1        | 4     | 48 | 30 | 22.4
  17879    | 397     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 22.4
  21320    | 445     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 22.4
  21231    | 457     | 4867  | 2878    | 29      | 1        | 1     | 12 | 12 | 22.4
  </code></pre>
  
  <p>How can I properly alter this so that the groupings of employee, manager and total reflect the grouped sum of strCount as well as the grouped sum of quant?</p>
  
  <p>Update:</p>
  
  <p>The logic here is this</p>
  
  <p>I want to get this same number of rows with the qualifying information as it is now, but my V1, V2 and V3 columns should be like this:</p>
  
  <p>V1: The sum of quantity for the grouping of employee/group/detail1/detail2 divided by the sum of the strCount of the same grouping (this is currently returning correctly)</p>
  
  <p>V2: The sum of quantity for the grouping of manager/group/detail1/detail2 divided by the sum of the strCount by the same grouping</p>
  
  <p>V3: The sum of total quantity for the group/detail1/detail2 divided by the sum of the total strCount by the same grouping</p>