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
David Grove wrote in message ...
>Folks,
>
>How does one apply a HAVING condition to a COUNT( DISTINCT col_name ) in a
>SELECT expression?
>
<SNIP>
I've mucked around with this and can't find any way of getting it working
either. Very curious! It should work, 'cos the query makes sense.
The closest I can get is
SELECT cust_no, COUNT(ssn )
FROM customer
GROUP BY cust_no
HAVING COUNT( DISTINCT ssn ) > 1
but then the selected count is wrong, although at least you only see the
matching cust_nos.
Hmmm - maybe a self-join to apply that as a filter?
Tried this (using on of my tables - I don't know what your key fields are so
substitute...):
select x0.cust_no, count(distinct x0.ssn)
from customer x0, customer x1
where x1.KEYFIELD = x0.KEYFIELD
group by 1
having count(distinct x1.ssn) > 1
Bastard! (oops, sorry Earle) No luck.
or a sub-query? That's starting to sound ugly... at least it's not
correlated:
select x0.cust_no, count(distinct x0.ssn)
from customer x0
where x0.cust_no in (
select x1.cust_no
from customer x1
group by 1
having count(distinct x1.ssn) > 1
)
group by 1
WOO HOO!
success...
Codd knows how efficient it will be with your tables and indexes...
Thank you, Mr. Hamm.
I finally arrived at:
SELECT DISTINCT cust_no
FROM customer as c1
WHERE 1 < (SELECT COUNT(DISTINCT c2.ssn)
FROM customer as c2
WHERE c1.cust_no = c2.cust_no);
Which doesn't do the complete job (it provides a list of cust_no's only,
with no indication of the actual number or values of duplicate ssn's. And
besides, it's correlated. Not pretty.
I like your proposal much better.
Still, it seems odd to me that Informix won't let me add a constraint to a
SELECTed aggregate function containing DISTINCT. As I recall, even MS
Access permits that (through the use of a synonym in the HAVING clause), but
I could be mistaken.
Does anyone know whether this feature (inability to constrain in HAVING
clause a DISTINCT aggregate function in the SELECT clause) is a consequence
of a SQL Standard, or just an Informix implementation issue?
Regards,
DG
"Andrew Hamm" <ahamm@sanderson.net.au> wrote in message
news:3a6f6da6$1@news.iprimus.com.au...
> David Grove wrote in message ...
> >Folks,
> >
> >How does one apply a HAVING condition to a COUNT( DISTINCT col_name ) in
a
> >SELECT expression?
> >
> <SNIP>
>
> I've mucked around with this and can't find any way of getting it working
> either. Very curious! It should work, 'cos the query makes sense.
>
> The closest I can get is
>
> SELECT cust_no, COUNT(ssn )
> FROM customer
> GROUP BY cust_no
> HAVING COUNT( DISTINCT ssn ) > 1>
> but then the selected count is wrong, although at least you only see the
> matching cust_nos.
>
> Hmmm - maybe a self-join to apply that as a filter?
>
> Tried this (using on of my tables - I don't know what your key fields are
so
> substitute...):
>
> select x0.cust_no, count(distinct x0.ssn)
> from customer x0, customer x1
> where x1.KEYFIELD = x0.KEYFIELD
> group by 1
> having count(distinct x1.ssn) > 1>
> Bastard! (oops, sorry Earle) No luck.
>
> or a sub-query? That's starting to sound ugly... at least it's not
> correlated:
>
> select x0.cust_no, count(distinct x0.ssn)
> from customer x0
> where x0.cust_no in (
> select x1.cust_no
> from customer x1
> group by 1
> having count(distinct x1.ssn) > 1
> )
> group by 1>
> WOO HOO!
> success...
>
> Codd knows how efficient it will be with your tables and indexes...
>
>
>
David Grove wrote in message ... >Thank you, Mr. Hamm. You are welcome! >Still, it seems odd to me that Informix won't let me add a constraint to a >SELECTed aggregate function containing DISTINCT. As I recall, even MS >Access permits that (through the use of a synonym in the HAVING clause), but >I could be mistaken. > >Does anyone know whether this feature (inability to constrain in HAVING >clause a DISTINCT aggregate function in the SELECT clause) is a consequence >of a SQL Standard, or just an Informix implementation issue? I'd guess an implementation issue. My minor experience with select distinct in the past showed that it's really cranky, as if there's a boolean in the optimiser that says "OK - seen a DISTINCT keyword! Reject all others at all cost!" and it looks like there is no consideration shown to occasional situations where it makes sense. I'm sure your original query makes sense, unless someone can prove otherwise.
In article <t6uck197to1247@corp.supernews.com>,
"David Grove" <david_grove@health.state.ak.us> wrote:
> Folks,
>
> How does one apply a HAVING condition to a COUNT( DISTINCT col_name )
in a
> SELECT expression?
This works in 9.21.
DROP TABLE Test_Data;--
CREATE TABLE Test_Data (
Id SERIAL PRIMARY KEY,
Val CHAR(2) NOT NULL,
Grp CHAR(2) NOT NULL
);--
INSERT INTO Test_Data
( Val, Grp )
SELECT T1.C || T2.C,
T3.C
FROM TABLE(SET{'A','A','B','B','C','C','D'}) T1 ( C ),
TABLE(SET{'A','A','B','B','C','C','D'}) T2 ( C ),
TABLE(SET{'A','A','B','B','C','C','D'}) T3 ( C )
WHERE T3.C < T2.C;--
SELECT T.Val,
COUNT(DISTINCT T.Grp )
FROM Test_Data T
GROUP BY T.Val;--
SELECT C.Val, C.Cnt
FROM TABLE(MULTISET( SELECT T.Val,
COUNT(DISTINCT T.Grp )
FROM Test_Data T
GROUP BY T.Val )) C ( Val, Cnt )
WHERE C.Cnt > 2;
This is an example of a "closed" query expression, which means
putting another SELECT statement in the FROM list. The implementation
isn't as good as it could be, but it will work pretty well for most
things, and you avoid all the temp table stuff.
Hope this helps!
KR
Pb
Sent via Deja.com
http://www.deja.com/