Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15

    Unanswered: Portable way to express COUNT(DISTINCT list) ?

    Not all databases support COUNT(DISTINCT list) where 'list' is a comma separated list of more than one columns. For example DB2 and PGSQL does not (at least in the versions I've seen). Does anybody know a standard way to write a portable version of that expression, that works with most db's?

    What I want to do:

    SELECT COUNT(DISTINCT a, b) FROM c GROUP BY d;

    ...but without using multiple column arguments to COUNT DISTINCT.

    Any ideas?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Portable way to express COUNT(DISTINCT list) ?

    Originally posted by rydenius
    Not all databases support COUNT(DISTINCT list) where 'list' is a comma separated list of more than one columns. For example DB2 and PGSQL does not (at least in the versions I've seen). Does anybody know a standard way to write a portable version of that expression, that works with most db's?

    What I want to do:

    SELECT COUNT(DISTINCT a, b) FROM c GROUP BY d;

    ...but without using multiple column arguments to COUNT DISTINCT.

    Any ideas?
    Oracle doesn't support that either. What one typically does, which I imagine would work on other DBMSs (but can't be sure) is:

    SELECT COUNT(DISTINCT a||b) FROM c GROUP BY d;

    If there are numbers and/or dates, these need to be converted to character first, which is where I imagine you will become DBMS-dependent again with date format masks etc.

  3. #3
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15
    Thanks a lot! But then I have another problem instead:

    COUNT(DISTINCT ...) should exclude rows with NULL values in column a or b caused by for example OUTER JOINS. Using the techique with '||' the NULL rows will be counted.

    In my case the columns a and b are both integers, so I tried something like

    a || '.' || b

    Do you have a smart solution for that too?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by rydenius
    Thanks a lot! But then I have another problem instead:

    COUNT(DISTINCT ...) should exclude rows with NULL values in column a or b caused by for example OUTER JOINS. Using the techique with '||' the NULL rows will be counted.

    In my case the columns a and b are both integers, so I tried something like

    a || '.' || b

    Do you have a smart solution for that too?
    Why not just add a WHERE clause:

    WHERE a IS NOT NULL
    AND b IS NOT NULL
    ...

  5. #5
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15
    Sure. Sometimes one just gets blind...

    Thanks!

  6. #6
    Join Date
    May 2003
    Posts
    5
    what about something like this:

    select count(*)
    from ( select a, b, count(*) from Table group by a, b )

    Not as pretty, but with a little massaging, that should work in most DBMS implementations....either as a dynamic view or as a correlated sub-query in the select line.

    Granted, efficiency is a little lacking.......

    Layne

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •