But I am using Postgresql 8.1 and it is not possible to write query as your one here.
On Thu, Oct 28, 2010 at 11:05 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Oct 19, 2010 at 7:56 PM, Merlin Moncure <mmonc...@gmail.com> > wrote: > > On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rumman...@gmail.com> wrote: > >> At present for reporting I use following types of query: > >> select crm.*, crm_cnt.cnt > >> from crm, > >> (select count(*) as cnt from crm) crm_cnt; > >> Here count query is used to find the total number of records. > >> Same FROM clause is copied in both the part of the query. > >> Is there any other good alternative way to get this similar value? > > > > Probably the best way to do this type of thing is handle it on the > > client. However, if you want to do it this way and your from clause > > is more complex than 'from table', you can possibly improve on this > > with a CTE: > > > > with q as (select * from <something expensive>) > > select q.* q_cnt.cnt from q, (select count(*) as cnt from q) q_cnt; > > > > The advantage here is that the CTE is materialized without having to > > do the whole query again. This can be win or loss depending on the > > query. > > What about > > select crm.*, sum(1) over () as crm_count from crm limit 10; > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
