select half of partition by for a sum()

Question

I have a query that is calculating the sum of a partition by and giving me a running total by a category. this part works well, now, I would like the sum of only the top 50% of the partition by. maybe a table example will show:

╔═══════╦══════════════════════════╦════════════════════════════╗
║ col_1 ║ sum of partition by      ║ sum of 50% of partition by ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 1     ║ 36  (this is 1+2+3+...8) ║ 10 (1+2+3+4)               ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 2     ║ 35 (this is 2+3+4+....8) ║ 9 (2+3+4)                  ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 3     ║ 34                       ║ 7 (3+4)                    ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 4     ║ 33                       ║ 4                          ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 5     ║ 32                       ║ null                       ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 6     ║ 31                       ║ null                       ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 7     ║ 30                       ║ null                       ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 8     ║ 29                       ║ null                       ║
╚═══════╩══════════════════════════╩════════════════════════════╝

right now I'm doing

 sum(col_)  over(partition by <another col> order by <a third col>) as [sum of partition by ]

then I later need to add another column for this calculation over the 25% so you get the idea.

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-05-26T11:38:28

You can use conditional logic by enumerating the rows and filtering. The following uses standard SQL syntax:

select x,
       sum(x) over (order by x desc),
       sum(x) filter (where seqnum <= 0.5 * cnt) over (order by x desc),
       sum(x) filter (where seqnum <= 0.25 * cnt) over (order by x desc)
from (select x, count(*) over () as cnt,
             row_number() over (order by x) as seqnum
      from generate_series(1, 8, 1) gs(x) 
     ) x
order by x;

Here is a db<>fiddle.

Although standard, Postgres is the only database that supports filter. The logic can easily be replaced with sum(case . . .).

Here is a db<>fiddle using SQL Server instead. The corresponding code is:

with gs as (
      select 1 as x union all
      select x + 1 from gs where x < 8
     )
select x,
       sum(x) over (order by x desc),
       sum(case when seqnum <= 0.5 * cnt then x end) over (order by x desc),
       sum(case when seqnum <= 0.25 * cnt then x end) over (order by x desc)
from (select x, count(*) over () as cnt,
             row_number() over (order by x) as seqnum
      from gs
     ) x
order by x;

select half of partition by for a sum()

1 Answers