Neil Saunders wrote:
Hi all,
I maintain an online property rental application. The main focus of the
UI is the search engine, which I'd now like to improve by allowing
filtering of the search results shown on some criteria, but provide a
count of the number of properties that meet that criteria.
> (snip)
...and so on. My question is simple - What's the best way to implement
this - Do I literally have to execute a count for the WHERE criteria
with the filter criteria tagged on, or is there some clever trick that
I'm not aware of? I'd rather not count in the application as I'd like to
plan for the day we have up to 100k properties (
Any suggestions gratefully received!
Here's the structure you want:
select
sum(case bedrooms when 1 then 1 else 0 end) as br1,
sum(case bedrooms when 2 then 1 else 0 end) as br2,
sum(case bedrooms when 3 then 1 else 0 end) as br3,
sum(case has_bbq when 1 then 1 else 0 end) as bbq,
sum(case has_pool when 1 then 1 else 0 end) as pool
from properties
in other words, you can put the criteria inside a case statement that
returns a 0 or 1, and use sum() over that case to count the rows that
returned a 1.
Adam
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
