My current solution is to issue a bunch of queries:
> SELECT make, count(*) FROM table WHERE >criteria< GROUP BY make ORDER BY count(*) DESC LIMIT 3;
make count
----------+---------
audi | 50
bmw | 40
vw | 30
SELECT color, count(*) FROM table WHERE >criteria< GROUP BY color
ORDER BY count(*) DESC LIMIT 3;
color count
-----------+------
red | 400
blue | 200
green | 100
Which will give me the top 3 counts of each column im interested in
for the >criteria< specified.
However, this is starting to become too slow (as there are about 10 of
these queries), and therefore I need to write an aggregate function
which lets me do:
>SELECT count_unique(make), count_unique(color) from table WHERE >criteria<;
After reading about aggregate functions, this should be possible, as
long as I can use a dictionary/hashmap type for the state<STYPE>
argument.
Is there such a type in postgresql that can be used in an aggregate
function, and if there isn't, how would it be possible to make one?
Also, do you think I'm going about this the wrong way, and there is a
much better solution that's I've neglected?
