Introduction

Transactions and table data

Consider a digital transaction in which a value is stored in one place and the context is stored elsewhere. For example, when a cash transaction occurs, we can represent the direction of the cash transaction in a table and the amounts in another table to dodge negative numbers. Similarly, there are a lot of forums and online commenting systems in which you can upvote or downvote. These votes will be stored separate from their individual count, thereby having entirely positive values, but they will be linked via foreign key to a table containing the original values of each vote, whether they are positive or negative.

Money transaction example

I encountered a similar problem related to different money transactions. The type of transaction would decide whether it was an incoming transaction or an outgoing one. Modeling this same circumstance, 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.

Grouping

When grouping, aggregate functions are key. We will be applying certain aggregate functions on a sample table containing financial details. The table's Data Definition Language (DDL) is provided in this post.

The structure of the table looks like:

Column

Type

TranID

int(11) Auto Increment

Subject

varchar(100)

Amount

decimal(7,2)

Currency

char(3)

Direction

int(11)

UserID

int(11)

TimeStamp

timestamp [CURRENT_TIMESTAMP]

For this table, I had added a new column that tells the direction. 1 being credit and 2 being debit. The values 1 and 2 do not mean anything to us, but they serve as a foreign key to another table that holds the values. I have intentionally used positive numbers to mimic the real-world scenario.

Consider the sample data set shown below, which represents values for one user:

TranID

Subject

Amount

Currency

Direction

UserID

TimeStamp

1

Salary

2000.00

USD

1

1

2017-05-12 09:15:56

2

Sold TV

150.00

GBP

1

1

2017-05-12 09:16:23

3

Mobile Bill

25.00

GBP

2

1

2017-05-12 09:19:03

4

House Rent

1500.00

USD

2

1

2017-05-12 09:21:03

5

Food

10.00

GBP

2

1

2017-05-12 09:24:33

6

Internet Bill

250.00

USD

2

1

2017-05-12 09:26:03

7

Value Added Tax

100.00

GBP

2

1

2017-05-12 09:26:45

8

Credit Card Bill

150.00

USD

2

1

2017-05-12 09:27:46

9

Import Charges

75.00

USD

2

1

2017-05-12 12:27:46

10

Cash Point Withdrawal

20.00

USD

2

1

2017-05-12 12:28:20

Getting the total amount.

To get the sum of all the amounts, use the SUM and GROUP BY functions. In the above case, we'll use the following query to get the sum of all the transactions, irrespective of the user, direction and/or the currency.

1
SELECTSUM(`Amount`)AS`Total`FROM`transactions`;

sql

The above will output something like:

Total

4185.00

Credit and Debit

This is not our ideal case. We have different rows getting different values. On top of all that, the amount debited should be negated from the value. In that case, we can split the income and expenditure using the GROUP BY function, which gives us the following query:

1
SELECTSUM(`Amount`)AS`Total`FROM`transactions`GROUPBY`Direction`;

sql

The above query will yield us something of the form:

Total

2150.00

2130.00

This table signifies a list of the sums of all transactions going in the same direction.

Different Currencies

With this, in the same way, we will be able to add another grouping by giving the column name separated by a comma. So if we need to add the next stage of grouping, let's add the Currency column this way:

Comparing to the initial data set, can you tell what effect the GROUP BY command had on our query? The values appear in the order dictated by the GROUP BY scheme. The output is sorted by Direction first and then by Currency, as seen by the fact that GBP comes before USD in the alphabet.

Can you see the effect of using AS in the query? Clearly, before we used this keyword, the query output kept the same order and values. However, we could not see the other information, such as the columns that you see now. Therefore, AS adds more information to the returned table in the form of output columns.

Multiplier Query

"Direction?" No idea.

But still, we have literally no clue what the direction means to us. Also, since we know 1 is positive (stays the same 1) and 2 is negative (should be converted into -1), we need to change them to their corresponding values. To make this kind of change, we use SQL JOINs if they are using the foreign key table relation or we use CASE … WHEN … END statements if we are only dealing with values.

Since our sample table only has values, we can build our multiplier.

1
2
3
4
CASEWHEN`Direction`=1THEN1WHEN`Direction`=2THEN-1END

sql

The above gives you the converted version of the Direction column, or the values. So having this code with the original Direction like this:

Combining Both

Looks like we have got what we needed: the multiplier! When the value of the result is multiplied with the amount, the total amount can be summed. With the multiplier as well as a parameter, the SQL query looks like:

Combine and Compress

With the currency, direction and multiplier, we have gotten to a point where we can understand what is happening, at least to an extent. So, let us add another field that shows the right total amount with the sign. We just need to multiply the total with the multiplier.

Pro Tip: If you try using the column Multiplier, it will not work. That column is for viewing purposes as an alias only. No column actually exists in that name.

Instead, we should redefine the contents of the column in order to generate the new column. Let's say the new column is Total and get rid of the old Total column. In short, we are multiplying what was already in Total with whatever we get in the multiplier query. That way we get the values we want, and we can get rid of the Direction since we are not using it.

Here, we see the effect of using GROUP BY. 15.00 GBP comes from adding 150 to -135. Similarly, 5.00 USD comes from adding 2000 to -1995.

This way, we can get the consolidated sum of different entities (Currency) based on their values (Direction). If we need to separate by the users as well, we just need to add another GROUP BY value of that column. So, that would be like: