I need to know how to do a SUM by two different things in the FEX.

How I have it written below it runs but SUMS the number of payments only by SSN so it is displayed incorrectly.

Coding Example

TABLE FILE PROBATE_TOTNUM2

SUM NUM_PAYMENTS AS 'PAYMENTS'
BY SSN
SUM MONEY_COLLECTED/D12.2M AS 'AMOUNT COLLECTED'
BY SSN
BY NAME
BY LETTER_TYPE2 AS 'COLLECTION TYPE'
BY TAX_TYPE_IND AS 'TAX TYPE'
WHERE MONEY_COLLECTED GT '0.00'

How this is displayed

SSN PAYMENTS NAME COLLECTION TYPE TAX TYPE AMOUNT COLLECTED
xxxxxxxxx 2 John Doe AMENDED CLAIMS INDIVIDUAL $560.00
xxxxxxxxx 3 Diane Doe CLAIMS FILED INDIVIDUAL $2,368.45
xxxxxxxxx 3 Henry Doe AMENDED CLAIMS INDIVIDUAL $3,162.00
xxxxxxxxx Henry Doe CLAIMS FILED INDIVIDUAL $1,581.00

TOTAL 8 $7,671.45


But I need it to show a 2 for Henry Doe - Amended Claims and 1 for Henry Doe - Claims Filed

So I tried to do this in the code and it will not run at all.

TABLE FILE PROBATE_TOTNUM2

SUM NUM_PAYMENTS AS 'PAYMENTS'
BY SSN
BY LETTER_TYPE2
SUM MONEY_COLLECTED/D12.2M AS 'AMOUNT COLLECTED'
BY SSN
BY NAME
BY LETTER_TYPE2 AS 'COLLECTION TYPE'
BY TAX_TYPE_IND AS 'TAX TYPE'
WHERE MONEY_COLLECTED GT '0.00'

Any suggestions?

This message has been edited. Last edited by: Julie H.,

For a multiverb request the sort fields in the first part must match in the second part.

TABLE FILE PROBATE_TOTNUM2

SUM NUM_PAYMENTS AS 'PAYMENTS'
BY SSN
BY LETTER_TYPE2 AS 'COLLECTION TYPE'
SUM MONEY_COLLECTED/D12.2M AS 'AMOUNT COLLECTED'
BY SSN
BY LETTER_TYPE2 AS 'COLLECTION TYPE'
BY NAME
BY TAX_TYPE_IND AS 'TAX TYPE'
WHERE MONEY_COLLECTED GT '0.00'

Thanks for your help!