CODEWITHSUNDEEP
sqlpostgresqlselectsum
Majid Hojati
Majid Hojati

Reputation: 1790

PostgreSQL using sum in where clause

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

Upvotes: 9

Views: 18422

Answers (3)

Hecatonchier
Hecatonchier

Reputation: 113

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

Upvotes: 10

Mureinik
Mureinik

Reputation: 311808

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;

Upvotes: 5

Fahim Al Mahmud Ashik
Fahim Al Mahmud Ashik

Reputation: 1121

Use Having clause

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

Upvotes: 6

Related Questions