Chips | 1 |

Chips | 3 |

Cookies | 3 |

Cookies | 2 |

Chocolate Bar | 5 |

Water | 4 |

Soda | 5 |

Chips | 1 |

Cookies | 4 |

Cookies | 1 |

Chocolate Bar | 2 |

Soda | 2 |

You want to find out how many of each items were sold to produce the output below:

Chips | 5 |

Cookies | 10 |

Chocolate Bar | 7 |

Water | 4 |

Soda | 7 |

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:

SORT FIELDS=(1,20,CH,A)

SUM FIELDS=(21,4,ZD)

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:

SORT FIELDS=(1,20,CH,A)

SUM FIELDS=NONE

###
**When SUM FIELDS Does not Work as Expected**

Sometimes, 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.

## No comments:

## Post a Comment