Count Unique Values

(4 posts) (3 voices)


  1. dharkness, Member

    I have a report that combines parent and child rows in a single table. In the summary row I added the count of each row's PK (autoincrement integer), but I'd like the parent PK column to count only unique values.

    For example,

    Parent  Child
        10     21
        10     22
        11     23
        12     24
         3      4

    Is this possible?

    Of course this doesn't belong to the Installation topic, but I don't see how to move it.

  2. myDBR Team, Key Master

    Easiest way to do it is calculate the distinct count of parent ID's first and then use dbr.summary.text to show it.

    myDBR Team

  3. shery, Member


    Can you please specify what will be the command for the above specified scenario?

  4. myDBR Team, Key Master

    Calculatethe distinct values and join them with the orginal data so you can use them with the dbr.summary.text:

    select 'dbr.max', 'dP';
    select 'dbr.max', 'dC';
    select 'dbr.summary.text', 'Parent', '[dP]';
    select 'dbr.summary.text', 'Child', '[dC]';
    select 'dbr.hidecolumns', 'dP'; select Parent, Child, dP, dC
    from parent_child
    join (
    select count(distinct Parent) as dP, count(distinct Child) as dC
    from parent_child
    ) as q;

    myDBR Team


You must log in to post.