pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcount( distinct x )
11+ messages / 4 participants
[nested] [flat]
* count( distinct x )
@ 2000-11-27 17:49 Anthony <lists@a1.org.uk>
2000-11-27 18:08 ` Re: count( distinct x ) Jose Rodrigo Fernandez Menegazzo <rocket@datascan-site.com>
2000-11-27 18:47 ` Re: count( distinct x ) Michael Fork <mfork@toledolink.com>
2000-11-27 19:10 ` Re: count( distinct x ) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 11+ messages in thread
From: Anthony @ 2000-11-27 17:49 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
@ 2000-11-27 18:08 ` Jose Rodrigo Fernandez Menegazzo <rocket@datascan-site.com>
2000-11-27 18:24 ` Re: count( distinct x ) Anthony <lists@a1.org.uk>
2 siblings, 1 reply; 11+ messages in thread
From: Jose Rodrigo Fernandez Menegazzo @ 2000-11-27 18:08 UTC (permalink / raw)
To: lists@a1.org.uk; +Cc: pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 18:08 ` Re: count( distinct x ) Jose Rodrigo Fernandez Menegazzo <rocket@datascan-site.com>
@ 2000-11-27 18:24 ` Anthony <lists@a1.org.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Anthony @ 2000-11-27 18:24 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
@ 2000-11-27 18:47 ` Michael Fork <mfork@toledolink.com>
2000-11-27 18:58 ` Re: count( distinct x ) Anthony <lists@a1.org.uk>
2 siblings, 1 reply; 11+ messages in thread
From: Michael Fork @ 2000-11-27 18:47 UTC (permalink / raw)
To: Anthony <lists@a1.org.uk>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 18:47 ` Re: count( distinct x ) Michael Fork <mfork@toledolink.com>
@ 2000-11-27 18:58 ` Anthony <lists@a1.org.uk>
2000-11-27 19:25 ` Re: count( distinct x ) Anthony <lists@a1.org.uk>
0 siblings, 1 reply; 11+ messages in thread
From: Anthony @ 2000-11-27 18:58 UTC (permalink / raw)
To: pgsql-sql
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.
> >
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 18:47 ` Re: count( distinct x ) Michael Fork <mfork@toledolink.com>
2000-11-27 18:58 ` Re: count( distinct x ) Anthony <lists@a1.org.uk>
@ 2000-11-27 19:25 ` Anthony <lists@a1.org.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Anthony @ 2000-11-27 19:25 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
@ 2000-11-27 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-27 19:20 ` Re: count( distinct x ) Anthony <lists@a1.org.uk>
2 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2000-11-27 19:10 UTC (permalink / raw)
To: Anthony <lists@a1.org.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 19:10 ` Re: count( distinct x ) Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-11-27 19:20 ` Anthony <lists@a1.org.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Anthony @ 2000-11-27 19:20 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
@ 2000-11-27 19:07 Anthony <lists@a1.org.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Anthony @ 2000-11-27 19:07 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
@ 2000-11-27 19:33 Anthony <lists@a1.org.uk>
2000-11-27 20:59 ` Re: count( distinct x ) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Anthony @ 2000-11-27 19:33 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: count( distinct x )
2000-11-27 19:33 Re: count( distinct x ) Anthony <lists@a1.org.uk>
@ 2000-11-27 20:59 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2000-11-27 20:59 UTC (permalink / raw)
To: Anthony <lists@a1.org.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2000-11-27 20:59 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-27 17:49 count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 18:08 ` Jose Rodrigo Fernandez Menegazzo <rocket@datascan-site.com>
2000-11-27 18:24 ` Anthony <lists@a1.org.uk>
2000-11-27 18:47 ` Michael Fork <mfork@toledolink.com>
2000-11-27 18:58 ` Anthony <lists@a1.org.uk>
2000-11-27 19:25 ` Anthony <lists@a1.org.uk>
2000-11-27 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-11-27 19:20 ` Anthony <lists@a1.org.uk>
2000-11-27 19:07 Re: count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 19:33 Re: count( distinct x ) Anthony <lists@a1.org.uk>
2000-11-27 20:59 ` Tom Lane <tgl@sss.pgh.pa.us>
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