Re: Counting distinct rows

John,

I think the SQL should be as follows:
select id, count(id) from basketupdates group by id;

which gives the following (example):

id | count
----+-------
1 | 3
2 | 1

In this case you wouldn't need the distinct as your automatically
eliminating duplicates through the use of the aggregation function (i.e.
group by --> count() )

hih

steve boyle

----- Original Message -----
From: "John Taylor" <postgres(at)jtresponse(dot)co(dot)uk>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, January 16, 2002 10:38 AM
Subject: [NOVICE] Counting distinct rows

>
> Hi,
>
> I'm having problems counting the number of distinct rows in a table.
>
> I've tried the following:
>
> select distinct on(id) count(id) from basketupdates order by id desc;
>
> but I get the error
>
> ERROR: Attribute basketupdates.id must be GROUPed or used in an aggregate
function
>
> I think it should say "MUST NOT", as if I remove the aggregate it works
fine.
> So I don't think this is the way to go about it.
>
> Can anyone tell how I should be going about counting the distinct ids in a
table ?
>
>
> Thanks
> JohnT
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>