pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcount() question
9+ messages / 8 participants
[nested] [flat]
* count() question
@ 1999-12-18 03:56 Mitch Vincent <mitch@venux.net>
1999-12-18 16:13 ` Re: [SQL] count() question Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Mitch Vincent @ 1999-12-18 03:56 UTC (permalink / raw)
To: pgsql-sql
Hey guys, I'd like to thank Tom for his suggestion on the joins earlier. I
more/less knew that about the joins but had built everything up and sort of
stuck the sub-selects in there as a band-aid to fix a problem. I re-did all
the queries using nothing but joins and boy, it's a lot faster.
I have one problem though, this is a type of search engine so when I search
I can't display all the results on one screen, it has to be limited. I use
the LIMIT directive in the queries to do so. Before I had been going 2
queries, one to get the total number of results and one to just get 10 of
those results.
So my question is, is there anyway you can get the total number of results
(using count() or something) and still use the limit directive in the same
query? I tried looking at the number of result rows too but 10 (the limit)
is all I come up with.
I would really like to avoid doing 2 full queries here, if anyone has any
suggestions please let me know.
Thanks!!
-Mitch
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] count() question
1999-12-18 03:56 count() question Mitch Vincent <mitch@venux.net>
@ 1999-12-18 16:13 ` Peter Eisentraut <peter_e@gmx.net>
1999-12-19 17:33 ` Re: [SQL] count() question Mitch Vincent <mitch@venux.net>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Eisentraut @ 1999-12-18 16:13 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
On 1999-12-17, Mitch Vincent mentioned:
> So my question is, is there anyway you can get the total number of results
> (using count() or something) and still use the limit directive in the same
> query? I tried looking at the number of result rows too but 10 (the limit)
> is all I come up with.
>
> I would really like to avoid doing 2 full queries here, if anyone has any
> suggestions please let me know.
Why do you want to avoid that? If you need two different results then you
probably have to use two different queries. I assume you need the count to
display something like "x matches found" and use LIMIT/OFFSET to step
through them page by page. In that case there is really no way but to
query twice. If your query is really complicated and slow you might want
to put the complete query results in a temporary table, and select the
count and the to-be-displayed data from there.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] count() question
1999-12-18 03:56 count() question Mitch Vincent <mitch@venux.net>
1999-12-18 16:13 ` Re: [SQL] count() question Peter Eisentraut <peter_e@gmx.net>
@ 1999-12-19 17:33 ` Mitch Vincent <mitch@venux.net>
1999-12-19 18:15 ` Re: [SQL] count() question Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 9+ messages in thread
From: Mitch Vincent @ 1999-12-19 17:33 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
> Why do you want to avoid that? If you need two different results then you
> probably have to use two different queries.
Speed, resource consumption and there might not be any need for it :-)
>I assume you need the count to
> display something like "x matches found" and use LIMIT/OFFSET to step
> through them page by page.
I do use LIMIT / OFFSET and that's part of the problem. I can't get a total
count from a query when I use LIMIT and OFFSET (at least I don't know how)..
> In that case there is really no way but to
> query twice. If your query is really complicated and slow you might want
> to put the complete query results in a temporary table, and select the
> count and the to-be-displayed data from there.
Indeed, that might be a better way to structure the search however at this
point I have to work withion what is already there (there is a huge
application built around the search engine). I am going to totally re-write
this but can't do that now, now I needed to add some functionality with a
minimum impact on the rest of the application.
If I have to do another query, so be it. I just wanted to make sure there
wasn't a way for me to do something like :
select * from applicants as a, count(*) as total where a.status = 'A' limit
10 offset 0
Of course I know I can't do that like that but thought there might be a
substitute or alternative way of getting the count from within the query.
-Mitch
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] count() question
1999-12-18 03:56 count() question Mitch Vincent <mitch@venux.net>
1999-12-18 16:13 ` Re: [SQL] count() question Peter Eisentraut <peter_e@gmx.net>
1999-12-19 17:33 ` Re: [SQL] count() question Mitch Vincent <mitch@venux.net>
@ 1999-12-19 18:15 ` Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 9+ messages in thread
From: Oleg Bartunov @ 1999-12-19 18:15 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
I recall Jan wrote about why it's impossible to get count() with
LIMIT clause. Check hackers mail-archive. But I do need also such a
feature.
regards,
Oleg
On Sun, 19 Dec 1999, Mitch Vincent wrote:
> Date: Sun, 19 Dec 1999 12:33:40 -0500
> From: Mitch Vincent <mitch@venux.net>
> To: Peter Eisentraut <peter_e@gmx.net>
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] count() question
>
> > Why do you want to avoid that? If you need two different results then you
> > probably have to use two different queries.
>
> Speed, resource consumption and there might not be any need for it :-)
>
> >I assume you need the count to
> > display something like "x matches found" and use LIMIT/OFFSET to step
> > through them page by page.
>
> I do use LIMIT / OFFSET and that's part of the problem. I can't get a total
> count from a query when I use LIMIT and OFFSET (at least I don't know how)..
>
> > In that case there is really no way but to
> > query twice. If your query is really complicated and slow you might want
> > to put the complete query results in a temporary table, and select the
> > count and the to-be-displayed data from there.
>
> Indeed, that might be a better way to structure the search however at this
> point I have to work withion what is already there (there is a huge
> application built around the search engine). I am going to totally re-write
> this but can't do that now, now I needed to add some functionality with a
> minimum impact on the rest of the application.
>
> If I have to do another query, so be it. I just wanted to make sure there
> wasn't a way for me to do something like :
>
> select * from applicants as a, count(*) as total where a.status = 'A' limit
> 10 offset 0
>
> Of course I know I can't do that like that but thought there might be a
> substitute or alternative way of getting the count from within the query.
>
> -Mitch
>
>
>
>
>
>
> ************
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 9+ messages in thread
* count question
@ 2008-04-09 01:51 novice <user.postgresql@gmail.com>
2008-04-09 02:11 ` Re: count question Craig Ringer <craig@postnewspapers.com.au>
2008-04-09 05:37 ` Re: count question Volkan YAZICI <yazicivo@ttmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: novice @ 2008-04-09 01:51 UTC (permalink / raw)
To: pgsql-sql
i have a table
CREATE TABLE meter
(
meter_id integer NOT NULL,
no_of_bays integer,
CONSTRAINT meter_pkey PRIMARY KEY (meter_id)
)
INSERT INTO meter(
meter_id, no_of_bays)
VALUES (5397, 2);
INSERT INTO meter(
meter_id, no_of_bays)
VALUES (5409, 3);
select meter_id, no_of_bays from meter;
meter_id | no_of_bays
----------+------------
5397 | 2
5409 | 3
Is it possible to write a query to produce:
meter_id | no_of_bays | bay_id
----------+------------+-----------
5397 | 2 | 5397-01
5397 | 2 | 5397-02
5409 | 3 | 5409-01
5409 | 3 | 5409-02
5409 | 3 | 5409-03
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: count question
2008-04-09 01:51 count question novice <user.postgresql@gmail.com>
@ 2008-04-09 02:11 ` Craig Ringer <craig@postnewspapers.com.au>
2008-04-09 03:34 ` Re: count question rdeleonp@gmail.com
1 sibling, 1 reply; 9+ messages in thread
From: Craig Ringer @ 2008-04-09 02:11 UTC (permalink / raw)
To: novice <user.postgresql@gmail.com>; +Cc: pgsql-sql
novice wrote:
> Is it possible to write a query to produce:
>
> meter_id | no_of_bays | bay_id
> ----------+------------+-----------
> 5397 | 2 | 5397-01
> 5397 | 2 | 5397-02
> 5409 | 3 | 5409-01
> 5409 | 3 | 5409-02
> 5409 | 3 | 5409-03
Sure. One way, not necessarily a particularly clever or efficient way,
is to do a join with:
generate_series(1, (select max(no_of_bays) from meter)) as i
and use a WHERE clause to select for `i <= no_of_bays'
eg:
SELECT
meter_id, no_of_bays, meter_id::text||'-'||i::text AS bay_id
FROM meter,
generate_series(1, (select max(no_of_bays) from meter)) as i
WHERE i <= no_of_bays;
--
Craig Ringer
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: count question
2008-04-09 01:51 count question novice <user.postgresql@gmail.com>
2008-04-09 02:11 ` Re: count question Craig Ringer <craig@postnewspapers.com.au>
@ 2008-04-09 03:34 ` rdeleonp@gmail.com
0 siblings, 0 replies; 9+ messages in thread
From: rdeleonp@gmail.com @ 2008-04-09 03:34 UTC (permalink / raw)
To: novice <user.postgresql@gmail.com>; +Cc: pgsql-sql
On 4/8/08, Craig Ringer <craig@postnewspapers.com.au> wrote:
> novice wrote:
>
> > Is it possible to write a query to produce:
> >
> > meter_id | no_of_bays | bay_id
> > ----------+------------+-----------
> > 5397 | 2 | 5397-01
> > 5397 | 2 | 5397-02
> > 5409 | 3 | 5409-01
> > 5409 | 3 | 5409-02
> > 5409 | 3 | 5409-03
>
> Sure. One way, not necessarily a particularly clever or efficient way,
> is to do a join with:
>
> generate_series(1, (select max(no_of_bays) from meter)) as i
>
> and use a WHERE clause to select for `i <= no_of_bays'
You have a problem, and it's bad design.
A bad design leads to these types of kludges.
Consider re-engineering your schema.
In any case, good luck.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: count question
2008-04-09 01:51 count question novice <user.postgresql@gmail.com>
@ 2008-04-09 05:37 ` Volkan YAZICI <yazicivo@ttmail.com>
2008-04-09 06:00 ` Re: count question ashish <ashish.karalkar@netcore.co.in>
1 sibling, 1 reply; 9+ messages in thread
From: Volkan YAZICI @ 2008-04-09 05:37 UTC (permalink / raw)
To: novice <user.postgresql@gmail.com>; +Cc: pgsql-sql
On Wed, 9 Apr 2008, novice <user.postgresql@gmail.com> writes:
> Is it possible to write a query to produce:
>
> meter_id | no_of_bays | bay_id
> ----------+------------+-----------
> 5397 | 2 | 5397-01
> 5397 | 2 | 5397-02
> 5409 | 3 | 5409-01
> 5409 | 3 | 5409-02
> 5409 | 3 | 5409-03
Shouldn't this function be the responsitibility of client software,
instead of database?
Regards.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: count question
2008-04-09 01:51 count question novice <user.postgresql@gmail.com>
2008-04-09 05:37 ` Re: count question Volkan YAZICI <yazicivo@ttmail.com>
@ 2008-04-09 06:00 ` ashish <ashish.karalkar@netcore.co.in>
0 siblings, 0 replies; 9+ messages in thread
From: ashish @ 2008-04-09 06:00 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttmail.com>; +Cc: novice <user.postgresql@gmail.com>; pgsql-sql
Volkan YAZICI wrote:
> On Wed, 9 Apr 2008, novice <user.postgresql@gmail.com> writes:
>
>> Is it possible to write a query to produce:
>>
>> meter_id | no_of_bays | bay_id
>> ----------+------------+-----------
>> 5397 | 2 | 5397-01
>> 5397 | 2 | 5397-02
>> 5409 | 3 | 5409-01
>> 5409 | 3 | 5409-02
>> 5409 | 3 | 5409-03
>>
>
> Shouldn't this function be the responsitibility of client software,
> instead of database?
>
>
> Regards.
>
May be this will help you
ashish=# select
meter_id,no_of_bays,meter_id||'-'||generate_series(1,no_of_bays) from meter;
meter_id | no_of_bays | ?column?
----------+------------+----------
5397 | 2 | 5397-1
5397 | 2 | 5397-2
5409 | 3 | 5409-1
5409 | 3 | 5409-2
5409 | 3 | 5409-3
With Regards
Ashish
===================================================================
sms START NETCORE to 575758 to get updates on Netcore's enterprise
products and services
sms START MYTODAY to 09845398453 for more information on our mobile
consumer services or go to http://www.mytodaysms.com
===================================================================
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-04-09 06:00 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-18 03:56 count() question Mitch Vincent <mitch@venux.net>
1999-12-18 16:13 ` Peter Eisentraut <peter_e@gmx.net>
1999-12-19 17:33 ` Mitch Vincent <mitch@venux.net>
1999-12-19 18:15 ` Oleg Bartunov <oleg@sai.msu.su>
2008-04-09 01:51 count question novice <user.postgresql@gmail.com>
2008-04-09 02:11 ` Re: count question Craig Ringer <craig@postnewspapers.com.au>
2008-04-09 03:34 ` Re: count question rdeleonp@gmail.com
2008-04-09 05:37 ` Re: count question Volkan YAZICI <yazicivo@ttmail.com>
2008-04-09 06:00 ` Re: count question ashish <ashish.karalkar@netcore.co.in>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox