Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group