2nd Tutorial about HeidiSQL and SteemSQL, Blockchain at your fingertips

Maybe you remember the last tutorial i have written, explaining the basics about HeidiSQL, talking about downloading and installing and also about setting up the connection to SteemSQL by @arcange. If not you can find it here

I concluded the last tutorial by executing some basic queries and showing the results.
This time I will go a little further and show you how to execute a little more complex queries and what to do with the data you get as a result.

SQL

But first lets take a short look at SQL and how this language is structured.
The thing that you will need most while working with Steemsql is queries in the form of select columns from tablename where filter criterias and then optional things like group by or order by.
SQL itself features a lot more possibilities but in order ot analyze and build up statistics this is basically what you need.
There is only one concept called join that you could possibly miss, this is a method to combine two or more tables on a given column, and therfore making it possible to use data out of both originating tables in your analysis.

You can also execute small commands like SUM() within the query directly.

Example: A query to get the sum of received transfers would look like this:
SELECT SUM(amount) from TxTransfers where "to" = 'utopian-io'
you can change the utopian-io to your name

The result of this would simply be a numeric value as can be seen in the screenshot

Playing around with Sums and where clauses

You want to see all your claimed rewards in one place ?
Try out this select statement : SELECT SUM(reward_sbd) AS SBD ,SUM(reward_steem) AS Steem, SUM(reward_vests) AS vests FROM TxClaimRewardBalances where "account" = 'utopian-io'

As you can see we have a lot of SUMs in there, because we want to grab every currency that steem uses. By the Way, Vests are invested Steem, so SteemPower. And for a better understanding we are renaming the columns into the according currency names. This is done with the AS statement.

This is thre result of the query:

Now we could bring in a time perspective, so what if you want to know what you earned in december?
We only would need to add another where clause by adding AND "timestamp" > '2017-11-30'

So the complete statement would look like this:
SELECT SUM(reward_sbd) AS SBD ,SUM(reward_steem) AS Steem, SUM(reward_vests) AS vests FROM TxClaimRewardBalances where "account" = 'utopian-io'AND "timestamp" > '2017-11-30'

This is the result of the query and as you can see the amounts diminished as was expected:

HeidiSQL features

AS you can see in the above queries can be quite long and therefore HeidiSQL has the option to save the SQL snippet for later use.
This can be done with a right-click and the save as snippet or with the file tab, like shown in the screenshot.

The good thing is that you got all your snippets directly at hand on the right side of your screen, along with a lot of useful informations about other SQL functions and keywords. You can see this in the next screenshot.

Conclusion

We learned to play around with where clauses and SUMs and also brought the time aspect into play. Since SQL Statements can get quite long and ugly we also learned how to save those for later reuse, we never want to invent the wheel twice.

Thanks alot for reading!

See you next time!
Lanatir

---

Posted on Utopian.io - Rewarding Open Source Contributors

---