Hi All,
I want to display as column for distinct sum of the column as below

col1 col2 col3
a 10 42
a 10 42
b 10 42
c 22 42

since we have duplicate values in the table we need to consider one time and summing of the all distinct values for col2

This can be done in oracle using total(distinct col1) but i am not sur how to do it in webfocus.

Coul you please let me know how to do that.

Thanks,
Raghu

This message has been edited. Last edited by: Kerry,

FST.
MAX.
LST.
DST.

etc...

Hi Tom,
Thanks for your reply.
but I want to display as distinct total.

Thanks,
Raghu

Can you show what you expect from your sample data ?

I think what Tom was saying is right, use DST. like:

TABLE FILE SYSTABLE
  SUM
    COMPUTE COL1/A1 = '';
    COMPUTE COL2/I3 = 0;
    COMPUTE COL3/I3 = 0;
  BY NAME NOPRINT
  ON TABLE SET HOLDLIST PRINTONLY
  ON TABLE SET XRETRIEVAL OFF
  ON TABLE HOLD AS HLDFILE FORMAT ALPHA
END
-RUN

-WRITE HLDFILE A 10 42
-WRITE HLDFILE A 10 42
-WRITE HLDFILE B 10 42
-WRITE HLDFILE C 22 42

TABLE FILE HLDFILE 
  SUM 
    DST.COL2 
    DST.COL3 
  BY COL1
END

And that will give you:

A=10 42
B=10 42
C=22 42

quote:

42

Hi Freste,
I need to display as below.

A=10 42
A=10 42
B=10 42
C=22 42

i need to display duplicate record but distinct total as a separate column


Tanks,
Rghu

OK, it's more clear now.
Below you'll find an attempt on how to get there:

-*--- Create sample flatfile with some records
TABLE FILE SYSTABLE
  SUM
    COMPUTE COL1/A1 = '';
    COMPUTE COL2/I3 = 0;
  BY NAME NOPRINT
  ON TABLE SET HOLDLIST PRINTONLY
  ON TABLE SET XRETRIEVAL OFF
  ON TABLE HOLD AS HLDFILE FORMAT ALPHA
END
-RUN

-WRITE HLDFILE A 10 
-WRITE HLDFILE A 10 
-WRITE HLDFILE B 10 
-WRITE HLDFILE C 22 
-RUN


DEFINE FILE HLDFILE
  RUNTOT/I5 = IF COL1 EQ LAST COL1 
             THEN LAST RUNTOT  
             ELSE LAST RUNTOT + COL2;
END

TABLE FILE HLDFILE 
  SUM 
    COMPUTE MATCHKEY/I1 = 1;    
    RUNTOT
  BY HIGHEST 1 COL1 NOPRINT
  ON TABLE HOLD AS RUNNING_TOTAL FORMAT ALPHA
END

DEFINE FILE HLDFILE
  MATCHKEY/I1 = 1;
END

MATCH
  FILE RUNNING_TOTAL
    SUM
      RUNTOT
    BY MATCHKEY
 RUN
  FILE HLDFILE 
    PRINT
      COL1
      COL2
    BY MATCHKEY
  AFTER MATCH HOLD 
END

TABLE FILE HOLD
  PRINT COL1 COL2 RUNTOT
END