CODEWITHSUNDEEP
sqlt-sql
Metaphor
Metaphor

Reputation: 6415

Is it possible to filter within a windowing function's partition

Here is a table I created to explain what I want to do:

create table #test (
    PlaceID int, 
    ItemID int, 
    ItemCount int, 
    Amount dec(11,2)
)

I would like to get 3 things:

  1. sum by Place
  2. sum by Place and Item
  3. sum by Place and Non-item

The first two are simple:

sum(Amount) over (partition by PlaceID) as PlaceAmount
sum(Amount) over (partition by PlaceID, ItemID) as PlaceItemAmount

But how do I get the sum for all items in the place that are NOT the current item?

Here is a SQL Fiddle with the data and query set up:

Upvotes: 1

Views: 93

Answers (3)

Serpiton
Serpiton

Reputation: 3684

SELECT
    PlaceID,
    ItemID,
    ItemCount,
    Amount,
    sum(ItemCount) over (partition BY PlaceID) AS PlaceItemCount,
    sum(Amount) over (partition BY PlaceID) AS PlaceAmount
  , sum(Amount) over (partition BY PlaceID, ItemID) AS PlaceItemAmount
  , sum(Amount) over (partition BY PlaceID)
  - sum(Amount) over (partition BY PlaceID, ItemID) AS PlaceItemAmountMinusGroup
  , sum(Amount) over (partition BY PlaceID) - Amount PlaceItemAmountMinusThis
FROM tblTest

PlaceItemAmountMinusGroup is the total amount by place without the total amount of ItemID
PlaceItemAmountMinusThis is the total amount by place without the amount of the row.

SQLFiddle demo

Upvotes: 1

paparazzo
paparazzo

Reputation: 45106

select t1.PlaceID, t1.ItemID, t1.ItemCount
     , t1.Amount as 'AmtMe'
     , SumPlace.sum as 'AmtPlace' 
     , SumPlace.sum - t1.Amount as 'AmtPlaceNoMe'
  from #test as t1
  join (select PlaceID, sum(Amount) as 'sum'
          from #test 
         group by PlaceID) as SumPlace 
    on t1.PlaceID = SumPlace.PlaceID

Upvotes: 1

Jaaz Cole
Jaaz Cole

Reputation: 3180

Does this do what you were expecting? Basically, take your whole (partition by place) and subtract your current (partition by place, item) to get the remainder. I would, however, mention that keeping this in a subquery so as to only run the windowed aggregates once per function and partition set.

Same can go for counts, using that logic as well.

select
    PlaceID,
    ItemID,
    ItemCount,
    Amount,
    PlaceItemCount,
    PlaceAmount,
    ItemAndPlaceAmount,
    PlaceAmount-ItemAndPlaceAmount as RemainderAmount
from (
  select
      PlaceID,
      ItemID,
      ItemCount,
      Amount,
      sum(ItemCount) over (partition by PlaceID) as PlaceItemCount,
      sum(Amount) over (partition by PlaceID) as PlaceAmount,
      sum(Amount) over (partition by PlaceID, ItemID) as ItemAndPlaceAmount
  from tblTest
  ) z

Upvotes: 1

Related Questions