View original (French)

DB2 query with COUNT and DISTINCT

Solved
Pitu Posted messages 1460 Status Contributor -  
Pitu Posted messages 1460 Status Contributor -
Hello, I have a small problem writing my DB2 query (on MVS/SPUFI). Table structure: (the columns xxx are not of interest to me, but exist in the table) 
Cle1 Cle2 Cle3 Rub1 Rub2 Rub3 Rub4 xxx1 xxx2 xxx3 ... A001 B001 C001 1234 5678 0000 aaa1 aaa2 aaa3 A001 B001 C001 1234 5678 0000 bbb1 bbb2 ccc3 A001 B001 C001 4321 5678 0000 ccc1 ddd2 ddd3 A001 B001 C001 1234 5678 0000 ... A002 B001 C001 2345 6789 1111 ... A002 B001 C001 2345 6789 1111 ... A002 B001 C001 2345 6789 1111 ... A003 B001 C001 3456 7890 1111 ... A003 B001 C001 3456 7890 1111 ... A003 B001 C001 3465 7890 2222 ... A003 B001 C001 3456 7890 3333 ... A003 B001 C001 6543 7890 1111 ... A003 B001 C001 3456 7890 1111 ...
The goal is to know whether, in this table, there exist rows for a key composed of the first 3 columns for which the values Rub1, Rub2 and Rub3 are not all the same. Ideally, I would like to have for the result the count of DISTINCT per group of key columns: 
A001 B001 C001 : 2
A002 B001 C001 : 1
A003 B001 C001 : 4
It seems I need to combine COUNT, DISTINCT and GROUP BY, but I’m stuck... I don’t have to do this in a single query; I can chain several queries, compare results of COUNT, etc... Thanks in advance if anyone can give me a hand. Configuration: Windows 2003 / Internet Explorer 6.0 -- (:*Þ Pitù Ni modo, ni même quasi modo

1 answer

  1.
    Pitu Posted messages 1460 Status Contributor 125
     
    Finally, I’ll provide the answer myself after many attempts ...

    SELECT key1, key2, key3, COUNT(*) FROM(
    SELECT DISTINCT
    key1, key2, key3, rub1, rub2, rub3, rub4
    FROM Table ) A
    GROUP BY
    key1, key2, key3
    ;

    --

      (:*Þ   Pitù 
    Ni modo, ni même quasi modo
    2