How do you sum the results of an alphanumeric field to not display just the last value.

For Example:
My output for a particular field is

X
X
X
X
Y
Y
Y
Z
Z


I just want it to be displayed as

X
Y
Z

We might need a bit more information here:

If the first instance of X were REFRIGERATOR and the second instance were RANGE, what would be the expected output?

BTW - please update your signature to include produst and release info.

SUM FIELD1
BY FIELD1 NOPRINT

You just need to summarize, sorting by unique values. If you just sum an alpha field, you always get the last value.

Sorry, I am a brand new intern this is all new to me. I will update my sign to make things easier. I work for an insurance company and I have a report that goes something like this:

INC_ID(NOT VISIBLE)(BY) LOCATION(BY) INC_COUNT(SUM) ADDRESS(SUM) CITY(SUM) COUNTY(SUM) TYPE(DETAIL
------------------- ------------ -------------- ------------ --------- ----------- -----------
xxxxxx xxxxxxxxx 30 xxxxxxx xxxxx xxxxxxx Multi-User
Multi-User
Multi-User
Single-User
Single-User

When I sum the TYPE field it will just give me in this case Single user which is the last record. I want it to diplays all of the records for this field that are used but only a single time for each with a count at the end (ex. Multi-User - 3 or Single-User - 2). Now there are about 20 possible result for the TYPE Field. Do I need to create a count for each possible result or is there and easier way to do this? I am using WEBFOCUS 7.1.4 and this particular report is going to be EXCEL Format.

The format for the previous post didnt come out as planned

quote:

INC_ID(NOT VISIBLE)(BY) LOCATION(BY) INC_COUNT(SUM) ADDRESS(SUM) CITY(SUM) COUNTY(SUM) TYPE(DETAIL

So is your code

TABLE FILE ....
SUM INC_COUNT NOPRINT
ADDRESS CITY COUNTY
BY LOCATION
PRINT TYPE
BY LOCATION
????

Does this help? Maybe you can use it as a model. The CAR file comes with WebFOCUS and you can use it to develop technique.

TABLE FILE CAR
SUM CNT.BODYTYPE
BY COUNTRY
BY BODYTYPE
END  

Run this code from an adhoc or console window to see what you get and compare it to what you want.

Okay here is the code:
[code]
SUM
CNT.INCIDENT_ID AS 'INCIDENT_COUNT'
ADDRESS
CITY
COUNTY_DESC AS 'COUNTY'
BY TOTAL HIGHEST INCIDENT_ID NOPRINT
BY LOCATION_CODE
PRINT
INC_TYPE AS 'Work Order Type'
BY TOTAL HIGHEST INCIDENT_ID NOPRINT
BY LOCATION_CODE
[code]

Now everything is displaying like it is suppose to except (INC_TYPE)... Say that the CNT.INCIDENT_ID Field has a record of 48. The report will display the 48 INC_TYPE's, many of these results have the same INC_TYPE. I do not want to display all 48 INC_TYPES seperately. I want to compress the similiar records together with a count to represent the number of INC_TYPE's. Instead of having INC_TYPE 'multi-user' shown 15 different times I want to to show 'multi-user - 15'.

SUM
CNT.INCIDENT_ID AS 'INCIDENT_COUNT'
ADDRESS
CITY
COUNTY_DESC AS 'COUNTY'
BY TOTAL HIGHEST INCIDENT_ID NOPRINT
BY LOCATION_CODE
BY INC_TYPE NOPRINT
PRINT
INC_TYPE AS 'Work Order Type'
BY TOTAL HIGHEST INCIDENT_ID NOPRINT
BY LOCATION_CODE

use by filed in first verb
like BY INC_TYPE NOPRINT

wf1998, That will not work.

You can not have more BYs in the first part of a multi-verb request than the last
and the BYs in the first part of the request must be the same and in the same order
as the corresponding BYs in the second part of the request.

For this request you do not even need a multi-verb request

DEFINE FILE CAR
MODEL_CNT/I5=1;
END
TABLE FILE CAR
SUM DEALER_COST
COMPUTE CNT_MODEL/I5=MODEL_CNT;
BY TOTAL HIGHEST CNT_MODEL NOPRINT
BY COUNTRY
BY CAR
END

Count distinct may help.

SUM CNT.DST....

Only thing is the 'pickyness' of where DST will work.