How to Use Sum() Over()

sum() Over(partition by order by a,b)

From 9.22. Window Functions:

When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame. An aggregate used
with ORDER BY and the default window frame definition produces a
“running sum” type of behavior, which may or may not be what's wanted.
To obtain aggregation over the whole partition, omit ORDER BY or use
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other
frame specifications can be used to obtain other effects.

When you ORDER BY fname, all rows within the partition have the "same" position in that order. You also PARTITION BY fname, so ORDER BY fname has no effect. That follows that the window frame is the same for all rows in the partition and therefore the function's results.

Then, when you ORDER BY o_details, it has an effect. The position of the rows in the partition is not the same for all of them anymore. And as the frame is relative to the position of the row in that order, it's different for almost each row and so are the function's results. I wrote almost, because this does not completely apply to the two fnames of 'eva' with the same o_details of 'coat'. They share one position. So for these two rows the function's results are again the same for the a fore mentioned reasons.

SUM OVER PARTITION BY

You could have used DISTINCT or just remove the PARTITION BY portions and use GROUP BY:

SELECT BrandId
       ,SUM(ICount)
       ,TotalICount = SUM(ICount) OVER ()    
       ,Percentage = SUM(ICount) OVER ()*1.0 / SUM(ICount) 
FROM Table 
WHERE DateId  = 20130618
GROUP BY BrandID

Not sure why you are dividing the total by the count per BrandID, if that's a mistake and you want percent of total then reverse those bits above to:

SELECT BrandId
           ,SUM(ICount)
           ,TotalICount = SUM(ICount) OVER ()    
           ,Percentage = SUM(ICount)*1.0 / SUM(ICount) OVER () 
    FROM Table 
    WHERE DateId  = 20130618
    GROUP BY BrandID

SQL SUM a CASE statement with an OVER clause

Unless you have an aggregation query (which you don't), this should work:

SUM(CASE WHEN ReceiptORIssueIndicator = '+'
         THEN NetQuantity ELSE 0
    END) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,

It is a bit hard to figure out what you really want. Your outer query has no aggregation, so it is not clear that aggregation is needed. In a query with aggregation:

SUM(SUM(CASE WHEN ReceiptORIssueIndicator = '+'
             THEN NetQuantity ELSE 0
        END)
   ) OVER (PARTITION BY SnapshotWeek, Plant, MaterialNumber ORDER BY Calendar.WeekSort) AS CumulativeSupplyQuantity,

This assumes that all the PARTITION BY and ORDER BY columns are GROUP BY keys. Otherwise, you need to use aggregation functions for them.

How to sum OVER (PARTITION BY DISTINCT) for Distinct Values

Use multiple different window specs to solve this one:

    select
      x.po_no, 

      x.OrdPOQty,
      SUM(pd.dely_qty) OVER(PARTITION BY x.po_no) as DelyPOQty,
      
      x.po_item_no,

      x.OrdItemQty,
      SUM(pd.dely_qty) OVER(PARTITION BY x.po_no, x.po_item_no) as DelyItemQty,
       
      x.qty, 
      pd.dely_no, 
      pd.dely_qty
    from 
      ( 
        SELECT 
          po.po_id, po.po_no, pt.po_item_id, pt.po_item_no, pt.qty, 
          SUM(pt.qty) OVER(PARTITION BY po.po_no) as OrdPOQty, 
          SUM(pt.qty) OVER(PARTITION BY po.po_no, pt.po_item_no) as OrdItemQty
        FROM PO inner join PO_ITEM pt on pt.po_id = po.po_id
      ) x
      inner join PO_ITEM_DELY pd on pd.po_item_id = x.po_item_id
    where 
      x.po_no = 'PO1'

Technically the partition by po_no is unnecessary because the where clause ensures there is only one, but i've left it in in case you want to expand the query to consider multiple po_no

If you will always only ever query one po_no:

    select
      x.po_no, 

      x.OrdPOQty,
      SUM(pd.dely_qty) OVER() as DelyPOQty,
      
      x.po_item_no,

      x.OrdItemQty,
      SUM(pd.dely_qty) OVER(PARTITION BY x.po_item_no) as DelyItemQty,
       
      x.qty, 
      pd.dely_no, 
      pd.dely_qty
    from 
      ( 
        SELECT 
          po.po_id, po.po_no, pt.po_item_id, pt.po_item_no, pt.qty, 
          SUM(pt.qty) OVER(PARTITION BY po.po_no) as OrdPOQty, 
          SUM(pt.qty) OVER(PARTITION BY po.po_no, pt.po_item_no) as OrdItemQty
        FROM PO inner join PO_ITEM pt on pt.po_id = po.po_id
      ) x
      inner join PO_ITEM_DELY pd on pd.po_item_id = x.po_item_id
    where 
      x.po_no = 'PO1'

wondering if there is an easier way of doing sums by more clever application of over partition by clause

Well, essentially with the basic form you end up with N repeats of a row, and you could count the repeats and divide the sum of the values in the group by the number of repeats of the group, so you're summing values that are a third of their original but repeated 3 times for the same overall sum.. But I do feel it makes a bigger mess than just doing the summing and counting at the level where there is no cartesian product, and then that result just gets carried out and repeated..

Or we could count only one of the items, presuming that every item will at least have an delivery #1:

select
  po.po_no, 

  SUM(CASE WHEN pd.dely_no = 1 THEN pt.qty ELSE 0 END) OVER(PARTITION BY po.po_no) as OrdPOQty,
  SUM(pd.dely_qty) OVER(PARTITION BY po.po_no) as DelyPOQty,
  
  pt.po_item_no,

  SUM(CASE WHEN pd.dely_no = 1 THEN pt.qty ELSE 0 END) OVER(PARTITION BY po.po_no, pt.po_item_no) as OrdItemQty,
  SUM(pd.dely_qty) OVER(PARTITION BY po.po_no, pt.po_item_no) as DelyItemQty,
   
  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'

If you add another table that causes the pd.dely_no to have repeats of value 1 per po/po+item partitions then you'll need to extend the CASE logic

sql sum over with a where condition

Hmmm . . . You can define the special group by counting the number of "10" in the month column:

select t.*,
       sum(count) over (partition by grp order by year, month)
from (select t.*,
             count(*) filter (where month = 10) as grp
      from t
     ) t;

You can also use arithmetic:

select t.*,
       sum(count) over (partition by floor( year * 100 - 10) / 100) order by year, month)
from t;

If you stored the date value as a proper date in a single column, this would be:

select t.*,
       sum(count) over (partition by date_trunc('year', datecol - interval '10 month') order by datecol)
from t;

---

Related Topics