Re: count( distinct x )

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(at)a1(dot)org(dot)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(at)a1(dot)org(dot)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.