SELECT
gr,
str,
count(*) OVER (PARTITION BY gr) a,
sum(1) OVER (PARTITION BY gr) b,
count(*) OVER (PARTITION BY gr ORDER BY str DESC) c,
sum(1) OVER (PARTITION BY gr ORDER BY str DESC) d
from (select number %3 gr, 'numb'||toString(number) str from numbers(9))
order by gr, str;
┌─gr─┬─str───┬─a─┬─b─┬─c─┬─d─┐
│ 0 │ numb0 │ 3 │ 3 │ 3 │ 3 │
│ 0 │ numb3 │ 2 │ 2 │ 2 │ 2 │
│ 0 │ numb6 │ 1 │ 1 │ 1 │ 1 │
│ 1 │ numb1 │ 3 │ 3 │ 3 │ 3 │
│ 1 │ numb4 │ 2 │ 2 │ 2 │ 2 │
│ 1 │ numb7 │ 1 │ 1 │ 1 │ 1 │
│ 2 │ numb2 │ 3 │ 3 │ 3 │ 3 │
│ 2 │ numb5 │ 2 │ 2 │ 2 │ 2 │
│ 2 │ numb8 │ 1 │ 1 │ 1 │ 1 │
└────┴───────┴───┴───┴───┴───┘
expected (PG):
SELECT
gr,
str,
count(*) OVER (PARTITION BY gr) a,
sum(1) OVER (PARTITION BY gr) b,
count(*) OVER (PARTITION BY gr ORDER BY str DESC) c,
sum(1) OVER (PARTITION BY gr ORDER BY str DESC) d
FROM
(SELECT generate_series%3 gr, 'numb'||generate_series str FROM generate_series(0,8)) t
order by gr, str
gr | str | a | b | c | d
----+-------+---+---+---+---
0 | numb0 | 3 | 3 | 3 | 3
0 | numb3 | 3 | 3 | 2 | 2
0 | numb6 | 3 | 3 | 1 | 1
1 | numb1 | 3 | 3 | 3 | 3
1 | numb4 | 3 | 3 | 2 | 2
1 | numb7 | 3 | 3 | 1 | 1
2 | numb2 | 3 | 3 | 3 | 3
2 | numb5 | 3 | 3 | 2 | 2
2 | numb8 | 3 | 3 | 1 | 1
(9 rows)
If it’s god’s will it will happen