CODEWITHSUNDEEP
sqlpostgresqlselectcountsubquery
gstackoverflow
gstackoverflow

Reputation: 37034

How to return several columns for subquery added to select?

I have a query like this:

select 
   e.field1,
   e.field2,
   (select count(field3) from tbl1 where someField = e.field1 group By someType ) as count_1,
   (select count(field4) from tbl1 where someField = e.field1 group By someType ) as count_2,
from
...

I don't like code duplication for counts but if I do smth like

select count(field3), count(field3) ....

in subquery postgres complains that subquery must return single column.

How to fix it ?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use a lateral join:

select e.field1,  e.field2, t1.*
from e left join lateral
     (seclect count(tbl1.field3) as count_1, count(tbl1.field4) as count_4
      from tbl1
      where tbl1.someField = e.field1
     ) t1
     on true;

You can also do this with aggregation:

select e.field1,  e.field2, t1.count_1, t1.count_2
from e left join
     (select tbl1.someField, count(tbl1.field3) as count_1, count(tbl1.field4) as count_2
      from tbl1
      group by tbl1.someField 
     ) t1
     on tbl1.someField = e.field1;

This could return NULL values, which you can convert to 0 if desired using coalesce().

Upvotes: 1

Related Questions