-1

I have a table

id    index   value
1       1        2
2       1        3 
3       2        6
4       3        8

I can do like this:

select sum(value) from table group by index

But what I want is that each row can go to multiple groups, pseudocode

 select sum(value) from table group by >= index

Basically the indexes are 1,2,3 and I want it to group these into 3 seperate groups.

  1. Sum of values where index is bigger/equal than 1
  2. Sum of values where index is bigger/equal than 2
  3. Sum of values where index is bigger/equal than 3

This must be a generic function, so I actually would not know the index level, as it is hardcoded here.

This is example output:

indexLevelBiggerEquals   sumValue
          1                 19          -- sum of all rows that are >= 1
          2                 14          -- sum of all rows that are >= 2
          3                 8           -- sum of all rows that are >= 3
ekad
  • 14,436
  • 26
  • 44
  • 46
Jaanus
  • 16,161
  • 49
  • 147
  • 202

2 Answers2

2

One sum for each "index >" group, use case to chose values to sum:

select sum(case when index >= 1 then value else 0 end) sum1,
       sum(case when index >= 2 then value else 0 end) sum2,
       sum(case when index >= 3 then value else 0 end) sum3
from table group by index

This is probably what you want:

select index,
       (select sum(value) from table where index >= t1.index)
from (select distinct index from table) t1;
jarlh
  • 42,561
  • 8
  • 45
  • 63
1

Use a window function, working over a limited selection of the table (note that the selection defaults to UNBOUNDED PRECEDING upto CURRENT ROW, which is what you want here, but you could specify something else) :

INSERT INTO tmp VALUES
(1,       1,        2),
(2,       1,        3),
(3,       2,        6),
(4,       3,        8)
;

SELECT index, SUM(value) OVER ( ORDER BY index DESC )
FROM tmp;


┌───────┬─────┐
│ index │ sum │
├───────┼─────┤
│     3 │   8 │
│     2 │  14 │
│     1 │  19 │
│     1 │  19 │
└───────┴─────┘
(4 rows)

EDIT :

Using other functions in the query :

SELECT index,
       COUNT(index),
       SUM(SUM(value)) OVER ( ORDER BY index DESC )                                                                             
FROM tmp 
GROUP BY index;
┌───────┬───────┬─────┐
│ index │ count │ sum │
├───────┼───────┼─────┤
│     3 │     1 │   8 │
│     2 │     1 │  14 │
│     1 │     2 │  19 │
└───────┴───────┴─────┘
(3 rows)

The SUM(SUM(value)) is needed because value must appear in a aggregate function. See here for a better explanation.

Community
  • 1
  • 1
Marth
  • 23,920
  • 3
  • 60
  • 72
  • 1
    You need another `order by index desc` for the actual query to make sure you get that result - but apart from that, I think that is a nifty trick! –  Feb 16 '15 at 08:08
  • Why there is 2 rows with index 1? – Jaanus Feb 16 '15 at 08:28
  • @Jaanus : Use `SELECT DISTINCT ON (index)` if you don't want multiple rows with the same index. As for why, window functions do not group rows together in the final result (unlike `GROUP BY`). – Marth Feb 16 '15 at 08:34
  • How can I use other functions like `count()`, I would like to count the grows in each group? I tried to use `count(index)`, but it sayd that `index must appear in the GROUP BY clause`. – Jaanus Feb 16 '15 at 09:13
  • @Jaanus : Edited my answer. Unsure what you mean by 'grows', but I added an example on how to use the `COUNT()` function. – Marth Feb 16 '15 at 09:25