Group and count multiple fields at once

Question

Is it possible to merge these queries so the search criteria is executed only once?

SELECT category, count(*)
FROM   ads
WHERE  [search criteria]
GROUP BY category

SELECT state, count(*)
FROM   ads
WHERE  [search criteria]
GROUP BY state

SELECT price_range, count(*)
FROM   ads
WHERE  [search criteria]
GROUP BY price_range

...

Plus about 10 more COUNTS

My aim is to create a filter similar to that is used in this website.

I'm thinking about using some kind of search engine (eg. Lucene) instead of relational database. New ideas are welcome.

Answer 1

Eduardo, Are you allowed to use a stored procedure? If so, you can execute the common part first.

SELECT ... 
FROM   ads WHERE  [search criteria]

Then you can do your multiple queries on the smaller table. You can also store the results and return the whole thing as one table with three columns:

1. type (category, state, price range, etc)
2. name (actually category/state/etc value)
3. count