Thread: count() question
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
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
> 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
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
Ok, so it's two queries. I have another intersting problem though.. Example of one of my count queries : select count (*) as total from applicants as a,applicants_states as s,resume_search as re where a.app_id=s.app_id and a.app_id=re.app_id and re.user_id=291 That's all well and fine except that it only counts the total from the applicants table, not the total from the rest of the query. I know I'm just not doing something right (obviously), can someone lend a hand? I've played with grouping but can't seem to get the syntax all right. Thanks (again)! -Mitch
"Mitch Vincent" <mitch@venux.net> writes:
> select count (*) as total from applicants as a,applicants_states as
> s,resume_search as re where a.app_id=s.app_id and a.app_id=re.app_id and
> re.user_id=291
> That's all well and fine except that it only counts the total from the
> applicants table, not the total from the rest of the query.
Er ... um ... what? The above will generate the count of the number of
joined tuples satisfying the WHERE condition. There is no "rest of the
query". I'm not following what you actually want to do, but evidently
this isn't it.
regards, tom lane