Thread: how to get the total number of records in report
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?
On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@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? Well, it looks like you're currently executing two sequential scans over the "crm" table. And you're including the total row-count as a separate column in every row you get back, although you really only need this piece of information once. Since you're fetching all of the "crm" table anyway, why not get rid of the COUNT(*) entirely and just keep a count on the client-side of the total number of rows you've fetched? Josh
Not actualy. I used pagination with limit clause in details query and I need the total number of records in the detail query.
On Mon, Oct 18, 2010 at 9:52 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
Well, it looks like you're currently executing two sequential scansOn Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@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?
over the "crm" table. And you're including the total row-count as a
separate column in every row you get back, although you really only
need this piece of information once.
Since you're fetching all of the "crm" table anyway, why not get rid
of the COUNT(*) entirely and just keep a count on the client-side of
the total number of rows you've fetched?
Josh
On Tue, Oct 19, 2010 at 1:18 AM, AI Rumman <rummandba@gmail.com> wrote:
Can you use a cursor? Roughly...
BEGIN;
DECLARE x CURSOR FOR SELECT * FROM crm;
MOVE FORWARD ALL IN x;
MOVE BACKWARD ALL IN x;
MOVE FORWARD 100 IN x;
FETCH FORWARD 100 FROM x;
CLOSE x;
COMMIT;
Not actualy. I used pagination with limit clause in details query and I need the total number of records in the detail query.
Can you use a cursor? Roughly...
BEGIN;
DECLARE x CURSOR FOR SELECT * FROM crm;
MOVE FORWARD ALL IN x;
MOVE BACKWARD ALL IN x;
MOVE FORWARD 100 IN x;
FETCH FORWARD 100 FROM x;
CLOSE x;
COMMIT;
Your application would need to get the actual text result from the "MOVE FORWARD ALL IN x;" statement to know the total number of records from your SELECT. After that, do your pagination via the "MOVE FORWARD 100 IN x;" and "FETCH FORWARD 100 FROM x;" statements.
HTH.
Greg
On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@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. merlin
On Tue, Oct 19, 2010 at 7:56 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@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
On Thu, Oct 28, 2010 at 1:05 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Oct 19, 2010 at 7:56 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >> On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@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; Hm, after a few quick tests it seems your approach is better in just about every respect :-). merlin
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 <robertmhaas@gmail.com> wrote:
What aboutOn Tue, Oct 19, 2010 at 7:56 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman <rummandba@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.
select crm.*, sum(1) over () as crm_count from crm limit 10;
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Oct 28, 2010 at 1:49 PM, AI Rumman <rummandba@gmail.com> wrote: > But I am using Postgresql 8.1 and it is not possible to write query as your > one here. with 8.1, you are limited to subquery approach, application derived count, plpgsql hacks, etc. merlin