I have started to explain tricks with SQL Server in a series of articles about a week or two ago. Today I will use a database, mentioned in a previous article.

Get data below average

The task is to get top values below the line of the average. For this, in our database, we will use the values in the population column of the table countries.

Thus, what we want? Something like this in the output:

What is this? These are the top 10 countries, which have population below the average in our table countries. They are united with the table Leagues, in the column LeagueName. So, we have somehow to make a nested query, which tells us how much is the average of the population from all countries. Furthermore, we need to tell the SQL Server to write “No league name” for the countries, which do not have a league name in the DB (pretty much a lot of country). Last but not least, we should cast the INT to BIGINT, in order to calculate the average value, because the SQL server needs it.

So, to summarize, this is how our query looks like:

That is pretty nice, if you run the query, using my database, you should get similar results. I say similar, because I have decided to enter the leagues for Venezuela and Saudi Arabia later, just for the sake of the example.

Left or Right Join

Now, let’s imagine that we would like to get only these countries, which have values for the league. Something like this:

A simple way to do it is to add another WHERE clause. A more sophisticated way is to take a look at the Join clause and decide to join only on these results, which have a country code. That is quite easy, as far as our join is exactly on the c.CountryCode column. With other words, think of the LEFT or RIGHT JOIN as a funnel, making a filter which gives you access to more or less data in the tables. It would be useful to change LEFT and RIGHT a few times, in order to understand how it works. Thus, for the aforementioned example, simply write the following:

That is all folks! 😀