PostgreSQL using sum in where clause

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

Answer 1

A bit late to the party, but for future reference, the following should work for a similar problem as the OP's:

SELECT id, sum(capacity)
FROM table
GROUP BY id
HAVING sum(capacity) < 26
ORDER by id ASC;

Use the PostgreSQL docs for reference to aggregate functions: https://www.postgresql.org/docs/9.1/tutorial-agg.html

Answer 2

Use Having clause

    select * from table order by id having sum(capacity)<X

Answer 3

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;