RE: Informix SQL - How to place a condition on COUNT( DISTINCT)
Does running it like this give you the results you need?
SELECT cust_no,ssn, COUNT( ssn )
FROM customer
GROUP BY cust_no,ssn
HAVING COUNT( ssn ) > 1;
-----Original Message-----
From: David Grove [mailto:david_grove@health.state.ak.us]
Sent: Wednesday, January 24, 2001 11:58 AM
To: informix-list@iiug.org
Subject: Informix SQL - How to place a condition on COUNT( DISTINCT)
Folks,
How does one apply a HAVING condition to a COUNT( DISTINCT col_name ) in a
SELECT expression?
For instance, if I wish to identify and resolve duplications between two
attributes, say "cust_no" and "ssn" (neither is a PK for the table), I might
try:
SELECT cust_no, COUNT( DISTINCT ssn )
FROM customer
GROUP BY cust_no;
I could examine the results and determine which cust_no's had multiple ssn's
associated with them. However, if the table were millions of rows, and
there were only a few cust_no's that had multiple ssn's, I would prefer to
see in the result only those rows in which the number of distinct ssn's was
greater than 1, as in:
SELECT cust_no, COUNT(DISTINCT ssn )
FROM customer
GROUP BY cust_no
HAVING COUNT( DISTINCT ssn ) > 1;
But that is a syntax error because "DISTINCT" is used more than once in the
query, and Informix seems not to permit the use of
"position-number-in-the-SELECT" as a synonym for the expression itself.
Can anyone suggest a way to formulate this query, properly, for Informix?
I'm wondering if it can be done without using multiple SELECT's and saving
intermediate results in a temporary table.
Thank you for any suggestions.
Regards,
David Grove
Thanks for your suggestion.
I think your proposal is close, but not quite what I'm looking for.
Consider a customer table (with many other unshown attributes, including a
sequence number which serves as PK) that contains rows with the following
values for two of the attributes (cust_no and ssn):
cust_no ssn
123 123-45-6789
123 123-45-6789
123 123-45-6789
123 987-65-4321
123 987-65-4321
123 567-89-1234
Note that there are 3 distinct ssn's for a particular cust_no. I believe
the result of your query would be as follows (note the single ssn is
missing):
cust_no ssn COUNT(ssn)
123 123-45-6789 3
123 987-65-4321 2
Further, if there were, say, 3 different ssn's, but each one occurred only
once, then that particular cust_no wouldn't show up at all in the results, I
think.
What I would prefer is either of the following:
cust_no # of distinct ssn's
123 3
or else:
cust_no distinct ssn's
123 123-45-6789
123 987-65-4321
123 567-89-1234
I appreciate you taking your valuable time to help. If I misunderstood your
thoughts, I apologize.
Regards,
DG
-----Original Message-----
From: Jay Roberts [mailto:Jay_Roberts@bourns.com]
Sent: Wednesday, January 24, 2001 2:04 PM
To: 'David Grove'; 'informix-list@iiug.org'
Subject: RE: Informix SQL - How to place a condition on COUNT( DISTINCT)
Does running it like this give you the results you need?
SELECT cust_no,ssn, COUNT( ssn )
FROM customer
GROUP BY cust_no,ssn
HAVING COUNT( ssn ) > 1;
-----Original Message-----
From: David Grove [mailto:david_grove@health.state.ak.us]
Sent: Wednesday, January 24, 2001 11:58 AM
To: informix-list@iiug.org
Subject: Informix SQL - How to place a condition on COUNT( DISTINCT)
Folks,
How does one apply a HAVING condition to a COUNT( DISTINCT col_name ) in a
SELECT expression?
For instance, if I wish to identify and resolve duplications between two
attributes, say "cust_no" and "ssn" (neither is a PK for the table), I might
try:
SELECT cust_no, COUNT( DISTINCT ssn )
FROM customer
GROUP BY cust_no;
I could examine the results and determine which cust_no's had multiple ssn's
associated with them. However, if the table were millions of rows, and
there were only a few cust_no's that had multiple ssn's, I would prefer to
see in the result only those rows in which the number of distinct ssn's was
greater than 1, as in:
SELECT cust_no, COUNT(DISTINCT ssn )
FROM customer
GROUP BY cust_no
HAVING COUNT( DISTINCT ssn ) > 1;
But that is a syntax error because "DISTINCT" is used more than once in the
query, and Informix seems not to permit the use of
"position-number-in-the-SELECT" as a synonym for the expression itself.
Can anyone suggest a way to formulate this query, properly, for Informix?
I'm wondering if it can be done without using multiple SELECT's and saving
intermediate results in a temporary table.
Thank you for any suggestions.
Regards,
David Grove
"Jay Roberts" <Jay_Roberts@bourns.com> wrote in message
news:94nnm1$51p$1@news.xmission.com...
>
> Does running it like this give you the results you need?
>
> SELECT cust_no,ssn, COUNT( ssn )
> FROM customer
> GROUP BY cust_no,ssn
> HAVING COUNT( ssn ) > 1;>
> -----Original Message-----
> From: David Grove [mailto:david_grove@health.state.ak.us]
> Sent: Wednesday, January 24, 2001 11:58 AM
> To: informix-list@iiug.org
> Subject: Informix SQL - How to place a condition on COUNT( DISTINCT)
>
>
> Folks,
>
> How does one apply a HAVING condition to a COUNT( DISTINCT col_name ) in a
> SELECT expression?
>
> For instance, if I wish to identify and resolve duplications between two
> attributes, say "cust_no" and "ssn" (neither is a PK for the table), I
might
> try:
>
> SELECT cust_no, COUNT( DISTINCT ssn )
> FROM customer
> GROUP BY cust_no;>
> I could examine the results and determine which cust_no's had multiple
ssn's
> associated with them. However, if the table were millions of rows, and
> there were only a few cust_no's that had multiple ssn's, I would prefer to
> see in the result only those rows in which the number of distinct ssn's
was
> greater than 1, as in:
>
> SELECT cust_no, COUNT(DISTINCT ssn )
> FROM customer
> GROUP BY cust_no
> HAVING COUNT( DISTINCT ssn ) > 1;>
> But that is a syntax error because "DISTINCT" is used more than once in
the
> query, and Informix seems not to permit the use of
> "position-number-in-the-SELECT" as a synonym for the expression itself.
>
> Can anyone suggest a way to formulate this query, properly, for Informix?
>
> I'm wondering if it can be done without using multiple SELECT's and saving
> intermediate results in a temporary table.
>
> Thank you for any suggestions.
>
> Regards,
>
> David Grove
>
Sorry for redundancy (or should I say reDUNCEancy) in the previous post. DG