Re: Counting unique rows as an aggregate.

>> I must be missing something, because I don't see why you couldn't do
>> SELECT count(distinct make), count(distinct color) from table WHERE
>> >criteria<;
>
> I didn't explain well, I want the count of each distinct value in a
> column, eg, if the color column has 50 rows, 20x'red', 10x'green',
> 20x'blue' - it will give me those results.
>
> SELECT count(distinct color) would return 3 - which is the count of
> distinct values, which is not what I want.

maybe this simple example helps you:

# create table colors (color text);
CREATE TABLE
# insert into colors select 'red';
INSERT 0 1
# insert into colors select 'red';
INSERT 0 1
# insert into colors select 'green';
INSERT 0 1
# insert into colors select 'yellow';
INSERT 0 1
# insert into colors select 'yellow';
INSERT 0 1
# insert into colors select 'yellow';
INSERT 0 1
# select count(1), color from colors group by color;
count | color
-------+--------
2 | red
3 | yellow
1 | green
(3 rows)

Jan