Having a little problem figuring this out. I've got the following 'report':

        job_nbr qty1    qty2
        aaa      1       5
        aaa      2       6
        bbb      2       7
        bbb      4       9 
        bbb      1       1
        ccc      2       2
        -------------------
total:           12      30

What I'd like to have is, under the job_nbr column, is the count of unique job_nbrs there (in the case of the above example, 3). Any idea how I can accomplish that?

It would be better if you give is the table request based on one of the sample databases

but you can do that by an extra query

TABLE FILE JOBS
COUNT JOB_NBR
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &JNUM.I5.

And than you get the value in the your ampervalue that can be used in the program

No need to do that extra processing, just use CNT.DST. prefix on the column name -

TABLE FILE GGSALES
SUM CNT.DST.ST AS 'Unique States'
    DOLLARS BUDDOLLARS
BY REGION
IF DATE FROM '19960101' TO '19971231'
END
-RUN

T

Of course Tony...
that's why I like this focal-point never to old to learn...

The other thing to remember is that -

hindsight is a wonderful thing, especially when viewed retrospectively!!

T

..... and talking of which, I think I misread your question Chris. I can't think of any method to get the distinct count in the position that you want it - e.g. in the same line as the totals underneath the job_nbr column (apart from intercepting the HTML output and editing it).

However, you could include it in a subfooting by using this code-

TABLE FILE GGSALES
SUM REGION DOLLARS BUDDOLLARS
    COMPUTE DST_REGION/I9 = IF REGION NE LAST REGION THEN 1 ELSE 0; NOPRINT
BY REGION NOPRINT
BY ST NOPRINT
IF DATE FROM '19960101' TO '19971231'
ON TABLE SUBFOOT
"There are <ST.DST_REGION Unique Regions"
END
-RUN

T

Tony

but if you do it my way, you get the value in a amper field and that can be placed wherever you like I suppose...

That's very true Frank, but you are double handling the data that way and if you have millions of records then it's not too productive if you only want the one summated value.

T

Thanks Frank and Tony. I ended up going with Frank's suggestion, and putting the totals in the footer of the report.

Thanks again.