There are a lot of instances where only the value is stored and the context is stored somewhere else. For example, when a cash transaction happens, the direction of the cash transaction will be noted in a separate table and there will not be any negative numbers. The same way, there's another case that could be possible. There are a lot of fora and online commenting systems where you can vote up or down. The votes will be stored elsewhere and they will be all positive integers and will be linked via foreign key to a different table, where the original values of each vote will be stored, either positive or negative.

I was working on a similar case, where it involves different money transactions, and the type of transaction will decide whether it is an incoming transaction or an outgoing one. We are going to look at three things today:

  • Connecting the transactions by their value.
  • Creating a multiplier based on the relation for the value.
  • Creating a consolidated sum grouped on different currencies, and users.

This article explains how you can make all the complex calculations using only the Database Server (MySQL, postgreSQL, Oracle), instead of processing the data on the server side application (PHP, Ruby, etc.) The main motive here is to make sure that the data stays in the access layer while business logic stays in the business layer.

Read the full article at PluralSight hack.guides() - Getting the consolidated sum of transactions grouped by currency just using MySQL.