Postgres Pro
Downloads
Home   >   mailing lists

Thread: Counting distinct rows

Counting distinct rows

From
John Taylor
Date:
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

Re: Counting distinct rows

From
Einar Karttunen
Date:
On 16.01.02 10:38 +0000(+0000), John Taylor wrote:
>
> 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;
>

SELECT count(distinct id) FROM basketupdates;
see http://www.postgresql.org/idocs/index.php?sql-expressions.html
for more information.

- Einar Karttunen

Re: Counting distinct rows

From
John Taylor
Date:
On Wednesday 16 January 2002 11:03, Einar Karttunen wrote:
> On 16.01.02 10:38 +0000(+0000), John Taylor wrote:
> >
> > 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;
> >
>
> SELECT count(distinct id) FROM basketupdates;
> see http://www.postgresql.org/idocs/index.php?sql-expressions.html
> for more information.
>

Thats it!

Thanks
JohnT

Re: Counting distinct rows

From
"Steve Boyle \(Roselink\)"
Date:
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@jtresponse.co.uk>
To: <pgsql-novice@postgresql.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: Counting distinct rows

From
Steve Crawford
Date:
Try:

select id, count(*) from basketupdates group by id order by id desc;


On Wednesday 16 January 2002 02:38, John Taylor wrote:
> 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@postgresql.org so that your
> message can get through to the mailing list cleanly