Question: PostgresSQL using sum in where clause

Question

PostgresSQL using sum in where clause

Answers 2
Added at 2017-01-05 22:01
Tags
Question

I have a table which has a numeric column named 'capacity'. I want to select first rows which the total sum of their capacity is no greater than X, Sth like this query

select * from table where sum(capacity )<X

But I know I can not use aggregation functions in where part.So what other ways exists for this problem?

Here is some sample data

id| capacity
1 | 12
2 | 13.5
3 | 15

I want to list rows which their sum is less than 26 with the order of id, so a query like this

 select * from table where sum(capacity )<26 order by id

and it must give me

id| capacity
1 | 12
2 | 13.5

because 12+13.5<26

Answers
nr: #1 dodano: 2017-01-05 22:01

Use Having clause

    select * from table order by id having sum(capacity)<X
nr: #2 dodano: 2017-01-05 22:01

You can use the window variant of sum to produce a cumulative sum, and then use it in the where clause. Note that window functions can't be placed directly in the where clause, so you'd need a subquery:

SELECT   id, capacity
FROM     (SELECT id, capacity, SUM(capacity) OVER (ORDER BY id ASC) AS cum_sum
          FROM   mytable) t
WHERE    cum_sum < 26
ORDER BY id ASC;
Source Show
◀ Wstecz