pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCounting distinct names
12+ messages / 7 participants
[nested] [flat]
* Counting distinct names
@ 2000-04-18 01:40 G. Anthony Reina <reina@nsi.edu>
0 siblings, 2 replies; 12+ messages in thread
From: G. Anthony Reina @ 2000-04-18 01:40 UTC (permalink / raw)
To: pgsql-sql
Let's say I have a table:
table1
=====
subject_name text
subject_age int4
test_trial int4
test_score int4
I'd like to find out how many times a unique 'subject_name' is in the
table. My SQL book says that I should be able to do this:
select COUNT(DISTINCT subject_name) from table1;
However, the psql program is giving me an "ERROR: parser: parse error
at or near "distinct"".
Is there another way to do this (or a problem with my query)?
Thanks.
-Tony Reina
Using [PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66]
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-18 02:16 Brett W. McCoy <bmccoy@chapelperilous.net>
parent: G. Anthony Reina <reina@nsi.edu>
1 sibling, 0 replies; 12+ messages in thread
From: Brett W. McCoy @ 2000-04-18 02:16 UTC (permalink / raw)
To: G. Anthony Reina <reina@nsi.edu>; +Cc: pgsql-sql
On Mon, 17 Apr 2000, G. Anthony Reina wrote:
> Let's say I have a table:
>
> table1
> =====
>
> subject_name text
> subject_age int4
> test_trial int4
> test_score int4
>
>
> I'd like to find out how many times a unique 'subject_name' is in the
> table. My SQL book says that I should be able to do this:
>
> select COUNT(DISTINCT subject_name) from table1;
SELECT
DISTINCT(subject_name),
COUNT(subject_name)
FROM
table1
GROUP BY
subject_name
;
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Anything that is good and useful is made of chocolate.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-18 03:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: G. Anthony Reina <reina@nsi.edu>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2000-04-18 03:25 UTC (permalink / raw)
To: G. Anthony Reina <reina@nsi.edu>; +Cc: pgsql-sql
"G. Anthony Reina" <reina@nsi.edu> writes:
> I'd like to find out how many times a unique 'subject_name' is in the
> table. My SQL book says that I should be able to do this:
> select COUNT(DISTINCT subject_name) from table1;
> However, the psql program is giving me an "ERROR: parser: parse error
> at or near "distinct"".
6.5 doesn't have aggregate(DISTINCT ...). 7.0 does, though.
If you don't want to upgrade right now, you could do something like
SELECT DISTINCT subject_name INTO temp_table FROM table1;
SELECT count(*) FROM temp_table;
DROP TABLE temp_table;
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-18 18:44 G. Anthony Reina <reina@nsi.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: G. Anthony Reina @ 2000-04-18 18:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> "G. Anthony Reina" <reina@nsi.edu> writes:
> > I'd like to find out how many times a unique 'subject_name' is in the
> > table. My SQL book says that I should be able to do this:
> > select COUNT(DISTINCT subject_name) from table1;
> > However, the psql program is giving me an "ERROR: parser: parse error
> > at or near "distinct"".
>
> 6.5 doesn't have aggregate(DISTINCT ...). 7.0 does, though.
>
> If you don't want to upgrade right now, you could do something like
>
> SELECT DISTINCT subject_name INTO temp_table FROM table1;
> SELECT count(*) FROM temp_table;
> DROP TABLE temp_table;
>
> regards, tom lane
Thanks Tom. I'll eventually upgrade, but still am a little wary considering
how many messages hackers continues to get on beta5. Perhaps, when 7.1 is
out I'll feel a little more secure about the upgrade.
-Tony
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-19 05:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: G. Anthony Reina <reina@nsi.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2000-04-19 05:05 UTC (permalink / raw)
To: G. Anthony Reina <reina@nsi.edu>; +Cc: pgsql-sql
"G. Anthony Reina" <reina@nsi.edu> writes:
> Thanks Tom. I'll eventually upgrade, but still am a little wary considering
> how many messages hackers continues to get on beta5. Perhaps, when 7.1 is
> out I'll feel a little more secure about the upgrade.
You meant 7.0.1, no? 7.1 will be a new major release with its own bugs...
Seriously, despite the amount of traffic about 7.0 issues, I believe
that 7.0 beta is already considerably more reliable than any prior
release ever was; and I believe the other developers share that opinion.
If you think 6.5.* is bug-free then you haven't been paying attention ;-).
We've fixed a huge number of problems since 6.5. Furthermore, how are
we going to flush out remaining issues if people don't try it? You're
not doing yourself or anyone else a service by staying away from 7.0.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-20 02:13 Christopher Sawtell <csawtell@xtra.co.nz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Christopher Sawtell @ 2000-04-20 02:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 19 Apr 2000, Tom Lane wrote:
> "G. Anthony Reina" <reina@nsi.edu> writes:
> > Thanks Tom. I'll eventually upgrade, but still am a little wary considering
> > how many messages hackers continues to get on beta5. Perhaps, when 7.1 is
> > out I'll feel a little more secure about the upgrade.
>
> You meant 7.0.1, no? 7.1 will be a new major release with its own bugs...
>
> Seriously, despite the amount of traffic about 7.0 issues, I believe
> that 7.0 beta is already considerably more reliable than any prior
> release ever was; and I believe the other developers share that opinion.
> If you think 6.5.* is bug-free then you haven't been paying attention ;-).
> We've fixed a huge number of problems since 6.5. Furthermore, how are
> we going to flush out remaining issues if people don't try it? You're
> not doing yourself or anyone else a service by staying away from 7.0.
OK, here are my comments and questions after just a very short play
yesterday evening with 7.0RC1. ( should I be getting the snapshot? )
1) There are files on the ftp sites which seem to be the "whole thing"
and what I suppose is the "whole thing" cut up into smaller sections.
Am I correct? Or do I need to d/l the smaller files as well?
A README_for_Download might be a good idea.
2) I couldn't find a --database-dir=[/var/lib/pgsql/data] option in the output
from ./configure --help. How can one configure pg to place it's database in a
defined place in the file system? btw the default of /var/lib/pgsql/data seems
pretty sensible.
3) Some of the scripts and binary files for the subsiduary tools have to be
installed by hand. If you need to know what, I'd be happy to re-install pg and
take notes.
4) For my tiny ultra-simple database it "just goes". ( On SuSE-6.3 )
5) Is the idea of putting the data on a raw partition on the horizon?
Thanks for a wonderful product.
--
Sincerely etc.,
NAME Christopher Sawtell - iOpen Technologies Ltd.
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL chris @ iopen . co . nz, csawtell @ xtra . co . nz
WWW http://www.iopen.co.nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-20 02:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Sawtell <csawtell@xtra.co.nz>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2000-04-20 02:50 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; +Cc: pgsql-sql
Christopher Sawtell <csawtell@xtra.co.nz> writes:
> 1) There are files on the ftp sites which seem to be the "whole thing"
> and what I suppose is the "whole thing" cut up into smaller sections.
Yes.
> Am I correct? Or do I need to d/l the smaller files as well?
> A README_for_Download might be a good idea.
Yo, Marc ... where's the documentation about the new tarfile sections?
> 2) I couldn't find a --database-dir=[/var/lib/pgsql/data] option in
> the output from ./configure --help. How can one configure pg to place
> it's database in a defined place in the file system? btw the default
> of /var/lib/pgsql/data seems pretty sensible.
The default location is PREFIX/data; I don't think there is a configure
option to change that without setting --prefix. However, you can set it
at runtime with the -D options to initdb and postmaster, so adding yet
another configure switch might be gilding the lily.
> 3) Some of the scripts and binary files for the subsiduary tools have
> to be installed by hand. If you need to know what, I'd be happy to
> re-install pg and take notes.
News to me, other than that the perl interface module usually has to be
installed separately because of permission issues. What did you see
missing?
> 5) Is the idea of putting the data on a raw partition on the horizon?
Not really. There are portability issues with that; furthermore, it's
not obvious that it'd be better. The system's been developed and tuned
to sit atop a Unix kernel's disk cache. Writing our own disk management
code would be a large investment of work and a large loss of portability
for very questionable benefits. It might happen someday, but I think
we have many higher-priority tasks ahead...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-20 13:07 Peter Eisentraut <e99re41@DoCS.UU.SE>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Peter Eisentraut @ 2000-04-20 13:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
On Wed, 19 Apr 2000, Tom Lane wrote:
> > 2) I couldn't find a --database-dir=[/var/lib/pgsql/data] option in
> > the output from ./configure --help.
> The default location is PREFIX/data; I don't think there is a configure
> option to change that without setting --prefix.
That's news to me. There is no default for the data storage location
anywhere that I can see. You always need to use the -D option or set
PGDATA in the environment. Of course PREFIX/data is the commonly used
place but it isn't any more special than any other location.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-20 14:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <e99re41@DoCS.UU.SE>
0 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2000-04-20 14:04 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> On Wed, 19 Apr 2000, Tom Lane wrote:
>> The default location is PREFIX/data; I don't think there is a configure
>> option to change that without setting --prefix.
> That's news to me. There is no default for the data storage location
> anywhere that I can see. You always need to use the -D option or set
> PGDATA in the environment.
Oh, you're right of course. My system behaves like there's a default,
but that's because I have PGDATA set in my .profile ;-)
*Should* there be a default location established by configure?
I think I'd prefer that to having the dependence on an environment var
... but on the other hand we couldn't really remove the envar without
breaking a lot of installations...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-21 02:30 SL Baur <steve@beopen.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: SL Baur @ 2000-04-21 02:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> writes:
> *Should* there be a default location established by configure?
I think so.
> I think I'd prefer that to having the dependence on an environment var
> ... but on the other hand we couldn't really remove the envar without
> breaking a lot of installations...
Keep the environment variable and let it override the default.
The test suite could use a sane default for PGLIB too. It seems
strange to compile and install postgres into a default location and
then have `make runtest' fail due to an environment variable not being
set. All the more so because the README file doesn't mention it.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-21 22:05 Peter Eisentraut <peter_e@gmx.net>
parent: SL Baur <steve@beopen.com>
0 siblings, 0 replies; 12+ messages in thread
From: Peter Eisentraut @ 2000-04-21 22:05 UTC (permalink / raw)
To: SL Baur <steve@beopen.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
SL Baur writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > *Should* there be a default location established by configure?
>
> I think so.
I don't think so, see separate message.
> The test suite could use a sane default for PGLIB too.
That's a completely different issue, because the files obtained that way
are necessarily fixed for a given installation (unless you are *very*
crafty). Peeking at the GNU/Autoconf recommendations, the files in
question should be installed into PREFIX/share by default and that default
noted inside the using program (initdb), with overriding possible. Of
course the name of the corresponding configure option --datadir isn't
going to make our life easier vis a vis the first issue above, but it's
still the way to go somehow.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Counting distinct names
@ 2000-04-21 22:06 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Peter Eisentraut @ 2000-04-21 22:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
Tom Lane writes:
> *Should* there be a default location established by configure?
Not IMO. Firstly (broken record time), configure should not be used to set
run-time options period. (And there's a single good reason for that: when
users build a package they won't know what options they need later on. I
get really annoyed at packages that do that.) Secondly, this will lead to
all kinds of funny problem reports because everyone will have a different
default. Worse yet, people might not even know what their default is
because they use a binary package. I think the current setup is the
cleanest by far because it emphasizes the independence of the data storage
location and the fact that multiple locations are equally valid.
> I think I'd prefer that to having the dependence on an environment var
Environment variables are a common way of pointing programs to their data.
Personally, I rarely bother with it and start the postmaster with -D
instead, then I know what I'm getting. I suppose there's no reason that
users couldn't do the same. The environment variable is only wildly useful
if people start and stop the server all the time.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2000-04-21 22:06 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-18 01:40 Counting distinct names G. Anthony Reina <reina@nsi.edu>
2000-04-18 02:16 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-04-18 03:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-18 18:44 ` G. Anthony Reina <reina@nsi.edu>
2000-04-19 05:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-20 02:13 ` Christopher Sawtell <csawtell@xtra.co.nz>
2000-04-20 02:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-20 13:07 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-04-20 14:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-21 02:30 ` SL Baur <steve@beopen.com>
2000-04-21 22:05 ` Peter Eisentraut <peter_e@gmx.net>
2000-04-21 22:06 ` Peter Eisentraut <peter_e@gmx.net>
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