Counting unique rows as an aggregate.

From: r_musta <zepolen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Counting unique rows as an aggregate.
Date: 2008-09-29 18:12:26
Message-ID: 41008430-014c-48ee-b7b6-10fd05b50b88@8g2000hse.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message x asasaxax 2008-09-29 18:19:04 Multiple querys
Previous Message Joshua Drake 2008-09-29 17:14:55 West: Second call for lightning talks