sql - How to obtain the number of tuple returned by each SELECT of an UNION query in postgreSQL? -

sql - How to obtain the number of tuple returned by each SELECT of an UNION query in postgreSQL? -

i have query consiting union of 2 select queries. each select query returns info. i'm interested in obtaining number of tuple returned each select query.

i want :

select table1.info info, num_result_tuple() table1, table2 table1.info < table2.info union select table3.info info, num_result_tuple() table3, table4 table3.info < table4.info

where num_result_tuple() must represent number of tuples found each simple query.

is there such function in postgresql ? or there way accomplish ?

you can utilize window function this:

select table1.info info, count(*) on () part_count table1, table2 table1.info < table2.info union select table3.info info, count(*) on () table3, table4 table3.info < table4.info;

you want union all instead of union. union remove duplicate rows between 2 select parts. if know can't have duplicates (or want homecoming them) union all faster.

if need remove duplicates original query, adding count() alter result. imagine next partial results:

first query:

info | part_count -----+----------- | 1

second query:

info | part_count -----+----------- | 2 b | 2

the union homecoming

info | part_count -----+----------- | 1 | 2 b | 2

the query without counts have returned

info ---- b

sql postgresql