From: Anthony <lists@a1.org.uk>
To: pgsql-sql@postgresql.org
Subject: Re: count( distinct x )
Date: Mon, 27 Nov 2000 19:33:11 +0000
Message-ID: <3A22B6F7.50E8C122@a1.org.uk> (raw)
References: <sa226059.073@10.0.0.4>
Kenn Thompson wrote:
> Ok- messy, but it works....
>
> CREATE VIEW testview AS
> select distinct area from areapostcode where postcode like 'BS1%';
>
> SELECT COUNT(*) FROM testview;
>
> kenn
>
> >>> Anthony <lists@a1.org.uk> 11/27/00 01:07PM >>>
> Kenn Thompson wrote:
>
> > What about
> >
> > select count(*) from (select distinct area from areapostcode where postcode like 'BS1%')
> >
>
> select count(*) from (select distinct area from areapostcode where
> postcode like 'BS1%');
> ERROR: parser: parse error at or near "select"
>
> Thanks, any more ideas?
>
> >
> > >>> Anthony <lists@a1.org.uk> 11/27/00 12:24PM >>>
> > Jose Rodrigo Fernandez Menegazzo wrote:
> >
> > > > The problem I have is with this statement:
> > > >
> > > > select count( distinct area ) from areapostcode where postcode like
> > > > 'BS1%'
> > > >
> > > > the above statement fails with
> > > > ERROR: parser: parse error at or near "distinct"
> > > >
> > > > I am not the greatest when it comes to SQL, but the pgsql docs implied
> > > > that the above would work.
> > > >
> > > > What I am trying to do is get a count of the no of matches from the
> > > > statement below
> > > > select distinct area from areapostcode where postcode like 'BS1%'
> > > >
> > > > Not the count of:
> > > > select area from areapostcode where postcode like 'BS1%'
> > > >
> > >
> > > I don't have where to try it, but have you tried:
> > >
> > > select distinct count(area) from areapostcode where postcode like 'BS1%'
> > >
> > > Rodrigo F.
> >
> > yes, it responds as if distinct is not in the query string.
> >
> > Thanks,
> > Bap.
CREATE VIEW testview AS
-> select distinct area from areapostcode where postcode like 'BS1%';
ERROR: DISTINCT not supported in views
I think it's time to get Mr. Sysadmin to upgrade to v7 ;)
Thanks all, if anyone has an ideas of how to get this working on 6.5.3, then please help.
I'll check back in the morning, and try any suggestions, if no joy will try to get PostgreSQL
upgraded.
Thanks all.
Bap.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: lists@a1.org.uk
Subject: Re: count( distinct x )
In-Reply-To: <3A22B6F7.50E8C122@a1.org.uk>
References: <sa226059.073@10.0.0.4> <3A22B6F7.50E8C122@a1.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox