Sum of rows as the last record from Postgresql query result

My Table column is like this, I want the sum of all records in the last row without using UNION

no_of_books
1
3
3
3
5


Expected result would be

no_of_books
1
3
3
3
5
15


I have tried like this, but looking for better fuctions in PostgreSQL like Oracle

select aa.no_of_books sum_prod from (SELECT prodcount
FROM "table1") aa
union all
select sum(bb.sum_prod) from (
select aa.no_of_books sum_prod from (SELECT prodcount
FROM "table1") aa ) bb


OR

select no_of_books
from table t
union all
select sum(no_of_books)
from table t ;


2 Answers
2

You can use rollup with subquery :

rollup

select coalesce(no_of_books, (select sum(no_of_books) from table t))
from table t
group by rollup(no_of_books);


However, this would be done with sum() directly :

sum()

select sum(no_of_books)
from table t
group by rollup(no_of_books);


Use rollup :

rollup

select sum(no_of_books)
from books t
group by rollup(no_of_books)


or cube

cube

select sum(no_of_books)
from books t
group by cube(no_of_books);


(SQL Fiddle Demo)

SQL Fiddle Demo

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.