getting the row count of a UNION query

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: getting the row count of a UNION query
Date: 2010-09-04 22:28:49
Message-ID: AANLkTinr15efxtJM8n45PX1Gc9cNXDJ+VRv+c45+4kD_@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Greetings,
I've got a bunch of tables with the same schema, that I'm accessing
via a UNION query. However, I'd like to get the count of returned
rows from this query. The best that I've been able to come up with at
this point is the following:
SELECT sum(counter) FROM ((select count(id) as counter from a0 ) UNION
ALL (select count(id) as counter from b0) UNION ALL (select count(id)
as counter from c0)) as foo;

From what I'm reading this is inefficient. Is there a better way to
get the total row count without resorting to executing each of the
subqueries separately and summing their rows?

thanks!

Browse pgsql-novice by date

  From Date Subject
Next Message Mladen Gogala 2010-09-05 02:32:31 Re: Raw Device Support
Previous Message Tom Lane 2010-09-04 18:25:31 Re: Raw Device Support