FW: 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:

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

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.

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?

----- 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

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

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