select whitout empty rows in postgresql

Question

I have a fun issue in postgresql.

select sum(value1) as v1,
   sum(value2) as v2,
   sum(value3) as v3,
   sum(value4) as v4,
   sum(value5) as v5,
   ...
from (select * from test limit 0)x;

result:

  v1 |  v2 |  v3 |  v4 |  v5 |  ...
-----+-----+-----+-----+-----+--...
     |     |     |     |     |  ...
(1 row)

Expected: (0 row).

Truth: (1 row).

I don't want to get empty rows. How can I get correct results for this case. Thanks.

The query will always return an empty row because limit 0 means "no rows at all". So if you don't want that, then why are you using limit 0 at all? The query makes no sense — user330315
– user330315, Commented Apr 1, 2016 at 9:55
I mean results for my query have some empty rows by using sum. Above query is a case to reproduct this issue, and we sure nothing to sum here. — Thịnh Nguyễn Thái
– Thịnh Nguyễn Thái, Commented Apr 1, 2016 at 10:10

user330315 · Accepted Answer · 2016-04-01 10:15:06Z

1

You can use a having clause to filter out null values:

select sum(id) 
from ( 
   select * 
   from test limit 0
) x
having sum(id) is not null;

answered Apr 1, 2016 at 10:15

user330315

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

3 Comments

Thịnh Nguyễn Thái Over a year ago

Thank for your help. I'm using this clause, but I hope have some ways are better, because I have 20+ sum clause on select, and I have to 20+ for having.

user330315 Over a year ago

@ThịnhNguyễnThái: you should have included that important bit of information in your question. Broadening the question after you have received an answer is not nice

Thịnh Nguyễn Thái Over a year ago

Yes, I noted. Thank you very much!

Collectives™ on Stack Overflow

select whitout empty rows in postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related