title: How to sum OVER (PARTITION BY DISTINCT) for Distinct Values
body: <p>I am looking for a clever way of using <code>Partition by Over</code> in SQL Server.</p>
  <p>I have 3 tables in SQL Server (all <code>*_id</code> columns below are just pseudo primary key)</p>
  <ul>
  <li>PO (po_id, po_no);</li>
  <li>PO_ITEM (po_item_id, po_id, po_item_no, qty); // stores ordered quantity for a PO ITEM</li>
  <li>PO_ITEM_DELY (po_item_dely_id, po_item_id, dely_no, dely_qty);   // stores delivered quantity for every item of PO per delivery no.</li>
  </ul>
  <pre><code>select
      po.po_no, pt.po_item_no, pt.qty, pd.dely_no, pd.dely_qty
  from 
      PO
  inner join 
      PO_ITEM pt on pt.po_id = po.po_id
  inner join 
      PO_ITEM_DELY pd on pd.po_item_id = pt.po_item_id
  where 
      po.po_no = 'PO1'
  </code></pre>
  <p>Result of this SQL query for reference:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>po_no</th>
  <th>po_item_no</th>
  <th>qty</th>
  <th>dely_no</th>
  <th>dely_qty</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>PO1</td>
  <td>PoI11</td>
  <td>300</td>
  <td>1</td>
  <td>210</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>PoI11</td>
  <td>300</td>
  <td>2</td>
  <td>48</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>PoI11</td>
  <td>300</td>
  <td>3</td>
  <td>55</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>PoI12</td>
  <td>100</td>
  <td>1</td>
  <td>100</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>PoI13</td>
  <td>250</td>
  <td>1</td>
  <td>150</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>PoI13</td>
  <td>250</td>
  <td>2</td>
  <td>100</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So in this example, the Total Ordered Quantity for PO1 is 650, but Total Delivered Qty is 663.</p>
  <p>Desired result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>po_no</th>
  <th>OrdPOQty</th>
  <th>DelyPOQty</th>
  <th>po_item_no</th>
  <th>OrdItemQty</th>
  <th>delyItemQty</th>
  <th>dely_no</th>
  <th>dely_qty</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>PO1</td>
  <td>650</td>
  <td>663</td>
  <td>PoI11</td>
  <td>300</td>
  <td>313</td>
  <td>1</td>
  <td>210</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>650</td>
  <td>663</td>
  <td>PoI11</td>
  <td>300</td>
  <td>313</td>
  <td>2</td>
  <td>48</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>650</td>
  <td>663</td>
  <td>PoI11</td>
  <td>300</td>
  <td>313</td>
  <td>3</td>
  <td>55</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>650</td>
  <td>663</td>
  <td>PoI12</td>
  <td>100</td>
  <td>100</td>
  <td>1</td>
  <td>100</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>650</td>
  <td>663</td>
  <td>PoI13</td>
  <td>250</td>
  <td>250</td>
  <td>1</td>
  <td>150</td>
  </tr>
  <tr>
  <td>PO1</td>
  <td>650</td>
  <td>663</td>
  <td>PoI13</td>
  <td>250</td>
  <td>250</td>
  <td>2</td>
  <td>100</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Now I am able to do this task by using subqueries:</p>
  <pre><code>with poOrdQtyDtl as (
  -- Form a Join between PO and PO_ITEM to get Total Ordered Qty Per PO
  select
      po.po_id,
      po.po_no,
      sum(pt.qty) OrdPoQty
  from PO
  inner join PO_ITEM pt on pt.po_id = po.po_id
  group by po.po_id, po.po_no
  )
  select
      poOrdQtyDtl.po_no [PO No.],
      poOrdQtyDtl.OrdPoQty [Ordered Qty For PO],
      sum(itemDely.currDelyQty) over (partition by poOrdQtyDtl.po_no) as [Delivered Qty For Po],
      itemDely.po_item_no [Item No.],
      itemDely.OrdItemQty [Ordred Item Qty],
      itemDely.DelItemQty [Delivered Item Qty],
      itemDely.dely_no [Dely No.],
      itemDely.currDelyQty [Item Qty Delivered in Current Dely]
  from poOrdQtyDtl
  inner join (
  -- Join PO_ITEM and PO_ITEM_DELY to get Item Quantity details
  select
      pt.po_id,
      pt.po_item_id,
      pt.po_item_no,
      pt.qty OrdItemQty,
      sum(pd.dely_qty) over (partition by pt.po_item_no) DelItemQty,
      pd.dely_no,
      pd.dely_qty currDelyQty
  from PO_ITEM pt
  inner join PO_ITEM_DELY pd on pd.po_item_id = pt.po_item_id
  ) itemDely on itemDely.po_id = poOrdQtyDtl.po_id
  WHERE poOrdQtyDtl.po_no = 'PO1'
  ;
  </code></pre>
  <p>However, I was just wondering if there is an easier way of doing sums by more clever application of <code>over partition by</code> clause. The main challenge is with the Query below, as I cannot use <code>distinct</code> in <code>partition by</code> clause.</p>
  <pre><code>select
      po.po_no,
      -- sum (pt.qty) over (partition by distinct po.po_no, pt.po_item_no) TotPoQOrd, -- INCORRECT
      sum (pt.qty) over (partition by po.po_no, pt.po_item_no) TotPoQOrd,
      sum(pd.dely_qty) over (partition by po.po_no) TotPoQDely,
      pt.po_item_no,
      pt.qty,
      sum(pd.dely_qty) over (partition by po.po_no, pt.po_item_no) TotItemQ,
      pd.dely_no,
      pd.dely_qty
  from PO
  inner join PO_ITEM pt on pt.po_id = po.po_id
  inner join PO_ITEM_DELY pd on pd.po_item_id = pt.po_item_id
  where po.po_no = 'PO1'
  </code></pre>