Thread: count( distinct x )
Apologies if this has been asked b4, but got this result when attemplting to search the archives on the website Not Found The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this server. Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 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%' Can anyone help? TIA Bap.
> 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.
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.
I think you want SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 27 Nov 2000, Anthony wrote: > Apologies if this has been asked b4, but got this result when > attemplting to search the archives on the website > > Not Found > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this > server. > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > 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%' > > Can anyone help? > > TIA > Bap. >
Michael Fork wrote: > I think you want > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' > psql still not happy :( SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'; ERROR: parser: parse error at or near "distinct" Thanks, Bap. > > Michael Fork - CCNA - MCP - A+ > Network Support - Toledo Internet Access - Toledo Ohio > > On Mon, 27 Nov 2000, Anthony wrote: > > > Apologies if this has been asked b4, but got this result when > > attemplting to search the archives on the website > > > > Not Found > > > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this > > server. > > > > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > > > 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%' > > > > Can anyone help? > > > > TIA > > Bap. > >
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.
Anthony <lists@a1.org.uk> writes:
> select count( distinct area ) from areapostcode where postcode like
> 'BS1%'
> the above statement fails with
> ERROR: parser: parse error at or near "distinct"
What Postgres version are you running? Support for count(distinct foo)
was added in 7.0, IIRC.
regards, tom lane
Tom Lane wrote: > Anthony <lists@a1.org.uk> writes: > > select count( distinct area ) from areapostcode where postcode like > > 'BS1%' > > the above statement fails with > > ERROR: parser: parse error at or near "distinct" > > What Postgres version are you running? Support for count(distinct foo) > was added in 7.0, IIRC. > > regards, tom lane select version(); version ------------------------------------------------------------------- PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) bugger! any ideas how to get same result at relative speed with different query? Thanks Tom.
Najm Hashmi wrote: > Anthony wrote: > > > Michael Fork wrote: > > > > > I think you want > > > > > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' > > > > > > > psql still not happy :( > > > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'; > > ERROR: parser: parse error at or near "distinct" > > > > Thanks, > > Bap. > > > > > > > > Michael Fork - CCNA - MCP - A+ > > > Network Support - Toledo Internet Access - Toledo Ohio > > > > > > On Mon, 27 Nov 2000, Anthony wrote: > > > > > > > Apologies if this has been asked b4, but got this result when > > > > attemplting to search the archives on the website > > > > > > > > Not Found > > > > > > > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this > > > > server. > > > > > > > > > > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > > > > > > > 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%' > > > > > > > > Can anyone help? > > > > > > > > TIA > > > > Bap. > > > > > > Hi, > I think this might work: It works on my machine, and I have postgres 7.xx > SELECT distinct(count(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' > > Regrads > Najm no, this one succedes, but returns the count of select area from areapostcode where postcode like 'BS1%' not the count of select distinct area from areapostcode where postcode like 'BS1%' but I have just replied to this list with the following select version(); version ------------------------------------------------------------------- PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row)
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.
Anthony <lists@a1.org.uk> writes:
> I think it's time to get Mr. Sysadmin to upgrade to v7 ;)
That's a good idea on many grounds, not only this one ;-)
However, if you really need a 6.5.* solution, you could do
SELECT DISTINCT foo INTO TEMP TABLE mytemp FROM ...SELECT COUNT(*) FROM mytemp;DROP TABLE mytemp;
regards, tom lane