You want to find out how many of each items were sold to produce the output below:
You do this using the SUM FIELDS statement in SYNCSORT. Let's say the first column is 20 characters and the second column is 4 columns. You first select the field you will use as the key. In this case, it is the items which is from column 1-20. The number of items sold is in column 21-24.
Your statements will be like this:
These statements will sum up columns 21-24 and put that value across the key.
You can also use the SUM FIELDS statement to remove duplicates. The following command will delete any record with duplicate keys:
When SUM FIELDS Does not Work as ExpectedSometimes, the SUM FIELDS will not work as expected. Say if your sum fields is as our example, 4 bytes and the total as it accumulates goes beyond 4 bytes, SYNCSORT will print out one line with the total and reset its counter and start accumulating again.
So if you have say 3 records each with the value of 5000 for a four byte field, the total will be 15000. Since adding two records will produce 10000, which is beyond 4 bytes, SUM FIELDS will produce 3 records, each with the value of 5000.
To fix this, you will need to expand your SUM FIELD length to at least 5 instead of 4.