Re: Multiple counts on criteria - Approach to a problem

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