pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcounting distinct rows on more than one column
8+ messages / 6 participants
[nested] [flat]
* counting distinct rows on more than one column
@ 2001-03-28 15:11 Dirk Lutzebaeck <lutzeb@aeccom.com>
2001-03-28 16:10 ` Re: counting distinct rows on more than one column Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 16:12 ` Re: counting distinct rows on more than one column Michael Fork <mfork@toledolink.com>
0 siblings, 2 replies; 8+ messages in thread
From: Dirk Lutzebaeck @ 2001-03-28 15:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
on 7.0.3 want to COUNT
SELECT DISTINCT a,b FROM t;
I can't find a solution because any combination with
count with more than one column gives syntax errors.
One solution would be to set a view:
CREATE VIEW v AS SELECT DISTINCT a,b FROM t;
and then
SELECT count(a) FROM v
but views don't support distinct in v7.0.3
Ok I could use a temporary table but my select distinct tends to give
large results.
Any clues?
Dirk
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: counting distinct rows on more than one column
2001-03-28 15:11 counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2001-03-28 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-03-28 16:10 UTC (permalink / raw)
To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: pgsql-sql
Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
> on 7.0.3 want to COUNT
> SELECT DISTINCT a,b FROM t;
In 7.1 you could do
select count(*) from (select distinct a,b from t) as t1;
In 7.0 and before I think you have no choice but to use a temp table.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: counting distinct rows on more than one column
2001-03-28 15:11 counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2001-03-28 16:12 ` Michael Fork <mfork@toledolink.com>
2001-03-28 19:32 ` Re: counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
1 sibling, 1 reply; 8+ messages in thread
From: Michael Fork @ 2001-03-28 16:12 UTC (permalink / raw)
To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: pgsql-sql
In 7.0.3, I believe the following would work:
SELECT count(distinct(a || b)) FROM t;
if subselects in from were supported in 7.0.3 as they are in 7.1, you
could do:
SELECT count(*) FROM (SELECT DISTINCT a,b FROM t) FROM x
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Wed, 28 Mar 2001, Dirk Lutzebaeck wrote:
>
> Hi,
>
> on 7.0.3 want to COUNT
>
> SELECT DISTINCT a,b FROM t;
>
> I can't find a solution because any combination with
> count with more than one column gives syntax errors.
>
> One solution would be to set a view:
>
> CREATE VIEW v AS SELECT DISTINCT a,b FROM t;
>
> and then
>
> SELECT count(a) FROM v
>
> but views don't support distinct in v7.0.3
>
> Ok I could use a temporary table but my select distinct tends to give
> large results.
>
> Any clues?
>
> Dirk
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: counting distinct rows on more than one column
2001-03-28 15:11 counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
2001-03-28 16:12 ` Re: counting distinct rows on more than one column Michael Fork <mfork@toledolink.com>
@ 2001-03-28 19:32 ` Dirk Lutzebaeck <lutzeb@aeccom.com>
2001-03-28 19:51 ` Re: counting distinct rows on more than one column Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Dirk Lutzebaeck @ 2001-03-28 19:32 UTC (permalink / raw)
To: Michael Fork <mfork@toledolink.com>; +Cc: pgsql-sql
Michael Fork writes:
> In 7.0.3, I believe the following would work:
>
> SELECT count(distinct(a || b)) FROM t;
Great, this works! I don't quite get it why...
Dirk
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: counting distinct rows on more than one column
2001-03-28 15:11 counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
2001-03-28 16:12 ` Re: counting distinct rows on more than one column Michael Fork <mfork@toledolink.com>
2001-03-28 19:32 ` Re: counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2001-03-28 19:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-03-28 19:51 UTC (permalink / raw)
To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: Michael Fork <mfork@toledolink.com>; pgsql-sql
Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
> Michael Fork writes:
>>> In 7.0.3, I believe the following would work:
>>>
>>> SELECT count(distinct(a || b)) FROM t;
> Great, this works! I don't quite get it why...
Michael really should not have proposed that solution without mentioning
its limitations: it's not actually counting distinct values of the column
pair a,b, but only of their textual concatenation. For example a = 'xy'
and b = 'z' will look the same as a = 'x' and b = 'yz'.
If there is some character you never use in column A, say '|', you
could do count(distinct(a || '|' || b)) with some safety, but this
strikes me as still a pretty fragile approach.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: counting distinct rows on more than one column
@ 2001-03-28 15:58 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Ansley @ 2001-03-28 15:58 UTC (permalink / raw)
To: 'Dirk Lutzebaeck' <lutzeb@aeccom.com>; +Cc: pgsql-sql
SELECT count(*) FROM (SELECT DISTINCT a, b FROM t) AS t2;
should give you what you want.
MikeA
>> -----Original Message-----
>> From: Dirk Lutzebaeck [mailto:lutzeb@aeccom.com]
>> Sent: 28 March 2001 16:11
>> To: pgsql-sql@postgresql.org
>> Subject: [SQL] counting distinct rows on more than one column
>>
>>
>>
>> Hi,
>>
>> on 7.0.3 want to COUNT
>>
>> SELECT DISTINCT a,b FROM t;
>>
>> I can't find a solution because any combination with
>> count with more than one column gives syntax errors.
>>
>> One solution would be to set a view:
>>
>> CREATE VIEW v AS SELECT DISTINCT a,b FROM t;
>>
>> and then
>>
>> SELECT count(a) FROM v
>>
>> but views don't support distinct in v7.0.3
>>
>> Ok I could use a temporary table but my select distinct tends to give
>> large results.
>>
>> Any clues?
>>
>> Dirk
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to
>> majordomo@postgresql.org
>>
_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: counting distinct rows on more than one column
@ 2001-03-28 20:43 Jeff Eckermann <jeckermann@verio.net>
2001-03-28 22:27 ` Re: RE: counting distinct rows on more than one column Jim Ballard <jballard@netezza.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Eckermann @ 2001-03-28 20:43 UTC (permalink / raw)
To: 'Dirk Lutzebaeck' <lutzeb@aeccom.com>; Michael Fork <mfork@toledolink.com>; +Cc: pgsql-sql
I don't think this will necessarily work:
field1 | field2
aa | ab
a | aab
These are two distinct rows, so should be counted as two.
The proposed method would count them as one.
You can get around this problem by doing:
count (distinct (a || x || b))
where x is some character not found in your data.
> -----Original Message-----
> From: Dirk Lutzebaeck [SMTP:lutzeb@aeccom.com]
> Sent: Wednesday, March 28, 2001 1:32 PM
> To: Michael Fork
> Cc: pgsql-sql@postgresql.org
> Subject: Re: counting distinct rows on more than one column
>
> Michael Fork writes:
> > In 7.0.3, I believe the following would work:
> >
> > SELECT count(distinct(a || b)) FROM t;
>
> Great, this works! I don't quite get it why...
>
> Dirk
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RE: counting distinct rows on more than one column
2001-03-28 20:43 RE: counting distinct rows on more than one column Jeff Eckermann <jeckermann@verio.net>
@ 2001-03-28 22:27 ` Jim Ballard <jballard@netezza.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim Ballard @ 2001-03-28 22:27 UTC (permalink / raw)
To: Jeff Eckermann <jeckermann@verio.net>; 'Dirk Lutzebaeck' <lutzeb@aeccom.com>; Michael Fork <mfork@toledolink.com>; +Cc: pgsql-sql
If the fields are fixed length character type, then the simpler
concatenation should work.
Actually, the requirement is only that all but the final field be fixed
length.
And if they aren't fixed length, you can cast them to be such, as long as
you know the maximum length of the string values, as in the following where
that maximum is 20 and we are looking at 3 fields:
select count(distinct (cast(field1 as char(20)) || cast(field2 as char(20))
|| field3)) from ...
Jim Ballard
----- Original Message -----
From: "Jeff Eckermann" <jeckermann@verio.net>
To: "'Dirk Lutzebaeck'" <lutzeb@aeccom.com>; "Michael Fork"
<mfork@toledolink.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 28, 2001 3:43 PM
Subject: [SQL] RE: counting distinct rows on more than one column
> I don't think this will necessarily work:
>
> field1 | field2
> aa | ab
> a | aab
>
> These are two distinct rows, so should be counted as two.
> The proposed method would count them as one.
> You can get around this problem by doing:
> count (distinct (a || x || b))
> where x is some character not found in your data.
>
> > -----Original Message-----
> > From: Dirk Lutzebaeck [SMTP:lutzeb@aeccom.com]
> > Sent: Wednesday, March 28, 2001 1:32 PM
> > To: Michael Fork
> > Cc: pgsql-sql@postgresql.org
> > Subject: Re: counting distinct rows on more than one column
> >
> > Michael Fork writes:
> > > In 7.0.3, I believe the following would work:
> > >
> > > SELECT count(distinct(a || b)) FROM t;
> >
> > Great, this works! I don't quite get it why...
> >
> > Dirk
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-03-28 22:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-28 15:11 counting distinct rows on more than one column Dirk Lutzebaeck <lutzeb@aeccom.com>
2001-03-28 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 16:12 ` Michael Fork <mfork@toledolink.com>
2001-03-28 19:32 ` Dirk Lutzebaeck <lutzeb@aeccom.com>
2001-03-28 19:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-28 15:58 RE: counting distinct rows on more than one column Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-03-28 20:43 RE: counting distinct rows on more than one column Jeff Eckermann <jeckermann@verio.net>
2001-03-28 22:27 ` Jim Ballard <jballard@netezza.com>
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