Tuesday, June 13, 2006

DB2 V8 Multiple DISTINCT clauses

Prior to Version 8..

SELECT DISTINCT C1, C2 FROM T1;
SELECT COUNT(DISTINCT C1) FROM T1;
SELECT C1, COUNT(DISTINCT C2) FROM T1 GROUP BY C1;
SELECT COUNT(DISTINCT(C1)), SUM(DISTINCT(C1)) FROM T1;

With Version 8..

SELECT DISTINCT COUNT(DISTINCT C1), SUM(DISTINCT C2) FROM T1;
SELECT COUNT(DISTINCT C1), AVG(DISTINCT C2) FROM T1 GROUP BY C1;
SELECT SUM(DISTINCT C1), COUNT(DISTINCT C1), AVG(DISTINCT C2) FROM T1 GROUP BY C1 HAVING SUM(DISTINCT C1)=1;

Not supported in Version 8..
SELECT COUNT(DISTINCT A1, A2) FROM T1 GROUP BY A2;
SELECT COUNT(DISTINCT(A1,A2)) FROM T1 GROUP BY A2;

No comments: