Postgres Pro
Downloads
Home   >   mailing lists

Thread: FW: Count & Distinct

FW: Count & Distinct

From
"anuj"
Date:

> >>> select count(distinct(row)) from table;
hi,
I have also tried but I am geting an error.

***************
tempdb=> \d tbltemp
Table    = tbltemp
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| id                               | int4                             |
4 |
| name                             | varchar()                        |
40 |
+----------------------------------+----------------------------------+-----
--+
tempdb=> select count(distinct(row)) from tbltemp;
ERROR:  parser: parse error at or near "distinct"
tempdb=>
***************

Thanks
Anuj


-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of sridhvenk@yahoo.com
Sent: Wednesday, August 23, 2000 9:12 AM
To: J. Atwood
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Count & Distinct


It works fine. Please try again




On Wed, 23 Aug 2000, J. Atwood wrote:

> Search a lot of archives and have come up with very little (much old)
>
> obviously
>
> >>> select count(distinct(row)) from table;
>
> Does not work so what is the work around? I have tried a bunch of stuff.
>
> Thanks,
> J
>
>

Re: FW: Count & Distinct

From
John McKown
Date:
I'm confused, your SELECT uses the variable "row", but there is no such
variable in your table?

John

On Thu, 24 Aug 2000, anuj wrote:

>
>
> > >>> select count(distinct(row)) from table;
> hi,
> I have also tried but I am geting an error.
>
> ***************
> tempdb=> \d tbltemp
> Table    = tbltemp
> +----------------------------------+----------------------------------+-----
> --+
> |              Field               |              Type                |
> Length|
> +----------------------------------+----------------------------------+-----
> --+
> | id                               | int4                             |
> 4 |
> | name                             | varchar()                        |
> 40 |
> +----------------------------------+----------------------------------+-----
> --+
> tempdb=> select count(distinct(row)) from tbltemp;
> ERROR:  parser: parse error at or near "distinct"
> tempdb=>
> ***************
>
> Thanks
> Anuj
>
>
> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of sridhvenk@yahoo.com
> Sent: Wednesday, August 23, 2000 9:12 AM
> To: J. Atwood
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Count & Distinct
>
>
> It works fine. Please try again
>
>
>
>
> On Wed, 23 Aug 2000, J. Atwood wrote:
>
> > Search a lot of archives and have come up with very little (much old)
> >
> > obviously
> >
> > >>> select count(distinct(row)) from table;
> >
> > Does not work so what is the work around? I have tried a bunch of stuff.
> >
> > Thanks,
> > J
> >
> >
>
>

Re: FW: Count & Distinct

From
"Ryan Williams"
Date:
I find that if I create the table described in the email, in psql I recieve
'ERROR:  Attribute 'row' not found'...

But this isn't exactly 'ERROR:  parser: parse error at or near
"distinct"'...


----- Original Message -----
From: "John McKown" <jmckown@prodigy.net>
To: "anuj" <Anuj@in-control.de>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, August 24, 2000 4:55 AM
Subject: Re: FW: [GENERAL] Count & Distinct


> I'm confused, your SELECT uses the variable "row", but there is no such
> variable in your table?
>
> John
>
> On Thu, 24 Aug 2000, anuj wrote:
>
> >
> >
> > > >>> select count(distinct(row)) from table;
> > hi,
> > I have also tried but I am geting an error.
> >
> > ***************
> > tempdb=> \d tbltemp
> > Table    = tbltemp
> >
+----------------------------------+----------------------------------+-----
> > --+
> > |              Field               |              Type                |
> > Length|
> >
+----------------------------------+----------------------------------+-----
> > --+
> > | id                               | int4                             |
> > 4 |
> > | name                             | varchar()                        |
> > 40 |
> >
+----------------------------------+----------------------------------+-----
> > --+
> > tempdb=> select count(distinct(row)) from tbltemp;
> > ERROR:  parser: parse error at or near "distinct"
> > tempdb=>
> > ***************
> >
> > Thanks
> > Anuj
> >
> >
> > -----Original Message-----
> > From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> > Behalf Of sridhvenk@yahoo.com
> > Sent: Wednesday, August 23, 2000 9:12 AM
> > To: J. Atwood
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Count & Distinct
> >
> >
> > It works fine. Please try again
> >
> >
> >
> >
> > On Wed, 23 Aug 2000, J. Atwood wrote:
> >
> > > Search a lot of archives and have come up with very little (much old)
> > >
> > > obviously
> > >
> > > >>> select count(distinct(row)) from table;
> > >
> > > Does not work so what is the work around? I have tried a bunch of
stuff.
> > >
> > > Thanks,
> > > J
> > >
> > >
> >
> >
>

Re: FW: Count & Distinct

From
"Ross J. Reedstrom"
Date:
On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote:
> I find that if I create the table described in the email, in psql I recieve
> 'ERROR:  Attribute 'row' not found'...
>
> But this isn't exactly 'ERROR:  parser: parse error at or near
> "distinct"'...

Right, that's the error you get from doing this in 6.5.x. The original
poster has already been advised to upgrade to 7.0.2.

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: FW: Count & Distinct

From
"Ryan Williams"
Date:
I'm running 7.0.2 (or at least I believe so).  Is there something that /I/
may be doing wrong?

----- Original Message -----
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: <pgsql-general@postgresql.org>
Sent: Thursday, August 24, 2000 8:44 AM
Subject: Re: FW: [GENERAL] Count & Distinct


> On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote:
> > I find that if I create the table described in the email, in psql I
recieve
> > 'ERROR:  Attribute 'row' not found'...
> >
> > But this isn't exactly 'ERROR:  parser: parse error at or near
> > "distinct"'...
>
> Right, that's the error you get from doing this in 6.5.x. The original
> poster has already been advised to upgrade to 7.0.2.
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: FW: Count & Distinct

From
Stephan Szabo
Date:
Yeah,
There is no column 'row'.  You're trying to use 'row' as
the value expression. Postgres figures it's a column
reference and tries to find it (and doesn't).

<general set function> ::=
 <set function type>
  <left paren> [<set quantifier>] <value expression> <right paren>

Set function type is COUNT, set quantifier is DISTINCT,
'row' is not a valid value expression in this case.
Note also, if I'm reading the spec right, * is not
valid in this context either.

On Thu, 24 Aug 2000, Ryan Williams wrote:

> I'm running 7.0.2 (or at least I believe so).  Is there something that /I/
> may be doing wrong?

Re: Count & Distinct

From
"J. Atwood"
Date:
Yup.. I (original poster) am using 6.5.3 and should/would/will head to 7.0.2
(already on another site in production). Just waiting for the time/need.

Thanks to everyone for their help.

J

> From: "Ryan Williams" <shrewsbury@louisiana.com>
> Date: Thu, 24 Aug 2000 12:08:46 -0700
> To: <pgsql-general@postgresql.org>
> Subject: Re: FW: [GENERAL] Count & Distinct
>
> I'm running 7.0.2 (or at least I believe so).  Is there something that /I/
> may be doing wrong?
>
> ----- Original Message -----
> From: "Ross J. Reedstrom" <reedstrm@rice.edu>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, August 24, 2000 8:44 AM
> Subject: Re: FW: [GENERAL] Count & Distinct
>
>
>> On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote:
>>> I find that if I create the table described in the email, in psql I
> recieve
>>> 'ERROR:  Attribute 'row' not found'...
>>>
>>> But this isn't exactly 'ERROR:  parser: parse error at or near
>>> "distinct"'...
>>
>> Right, that's the error you get from doing this in 6.5.x. The original
>> poster has already been advised to upgrade to 7.0.2.
>>
>> Ross
>>
>> --
>> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
>> NSBRI Research Scientist/Programmer
>> Computer and Information Technology Institute
>> Rice University, 6100 S. Main St.,  Houston, TX 77005
>
>

RE: FW: Count & Distinct

From
"anuj"
Date:

>> I find that if I create the table described in the email, in psql I
recieve
>> 'ERROR:  Attribute 'row' not found'...
>>
>> But this isn't exactly 'ERROR:  parser: parse error at or near
>> "distinct"'...

> >> > >select count(distinct(row)) from table;

>Right, that's the error you get from doing this in 6.5.x. The original
>poster has already been advised to upgrade to 7.0.2.

YES, previously I was using the 6.5.3-6; now I am using 7.0.2.
Now problem have sort-out thanks all of list member.
Regards,
Anuj

Re: FW: Count & Distinct

From
Nils Zonneveld
Date:

anuj wrote:
>
> > >>> select count(distinct(row)) from table;
> hi,
> I have also tried but I am geting an error.
>

It's true that you get an error from trying to execute the query above;
'distinct' is not a function but a keyword, count() is a agregate
function. Following this the correct notation for your query would be:

select count(distinct <row>) from <table>;

I tested this on postgreSQL 7.02 and it works.

I tried this:


NAW=# \d tmpTable
          Table "tmptable"
 Attribute |    Type     | Modifier
-----------+-------------+----------
 id        | integer     |
 name      | varchar(25) |


NAW=# select * from tmpTable;
 id |   name
----+-----------
  1 | Jansen
  2 | Jansen
  3 | Pietersen
  4 | Pietersen
(4 rows)


NAW=# select count(name) from tmpTable;
 count
-------
     4
(1 row)


NAW=# select count(distinct name) from tmpTable;
 count
-------
     2
(1 row)


Success and kind regards,

Nils Zonneveld
--
"Misschien is niets geheel waar, en zelfs dat niet"
Multatuli (Eduard Douwes Dekker) - Idee 1