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

Help us help you - please share some sample data and the result you'd like to get for this sample. — Mureinik
– Mureinik, Commented Jan 5, 2017 at 21:24
I'm a little confused about what you want here, but this is my best guess: You want to select rows from the table in order of the id column, summing up capacity as you go and stop selecting before the sum reaches 26? — David Bachmann Jeppesen
– David Bachmann Jeppesen, Commented Jan 5, 2017 at 21:48
@DavidBachmannJeppesen yes this is exactly what I want and seems Mureinik's answer is the solution — Majid Hojati
– Majid Hojati, Commented Jan 5, 2017 at 21:50

Hecatonchier · Accepted Answer · 2021-02-16 11:18:59Z

10

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

answered Feb 16, 2021 at 11:18

Hecatonchier

1133 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahim Al Mahmud Ashik · Accepted Answer · 2017-01-05 21:32:07Z

6

Use Having clause

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

edited Jan 5, 2017 at 21:32

answered Jan 5, 2017 at 21:28

Fahim Al Mahmud Ashik

1,12110 silver badges26 bronze badges

3 Comments

Majid Hojati Over a year ago

I used this but it say's 'ERROR: column "id" must appear in the GROUP BY clause or be used in an aggregate function Position: 8' id is its primery key

Fahim Al Mahmud Ashik Over a year ago

see the edits.When i wrote the answer you only provided the first snippet.So,after your edit i also modified the answer

Majid Hojati Over a year ago

I have no idea why your answer gives me that error again, but Mureiniks answer does not

Mureinik · Accepted Answer · 2017-01-05 21:34:53Z

5

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;

answered Jan 5, 2017 at 21:34

Mureinik

316k54 gold badges399 silver badges405 bronze badges

3 Comments

Majid Hojati Over a year ago

in first select capacity should not be cum_sum?

Mureinik Over a year ago

@MajidHojati In the sample you gave in the question, you want to display the capacity per row, not the cumulative capacity, so that's what I put in the select list. You could, of course, either add cum_sum or replace capacity with it - it all depends on what result you want to display.

Charming Robot Over a year ago

Not sure cum sum is the best naming for this :P

Collectives™ on Stack Overflow

PostgreSQL using sum in where clause

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related