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

   Hi,

   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

Reply

You must log in to post.