Testing the model

The model part of ASP.NET MVC is best tested in conjunction with the database.

public class ShipRepository : IShipRepository
{
    private string ConnectionString;

    public ShipRepository()
    {
        ConnectionString = ConfigurationManager
            .ConnectionStrings["Database"].ToString();
    }

    public ShipRepository(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public IEnumerable<Ship> GetList()
    {
        const string query = "SELECT Name FROM Ships";
        var ships = new List<Ship>();
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand(query, connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    ships.Add(new Ship { Name = reader.GetString(0) });
                }
            }
        }
        return ships;
    }

    public void Insert(Ship ship)
    {
        var ships = new List<Ship>();
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("spInsertShip", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Name", ship.Name);
                command.ExecuteNonQuery();
            }
        }
    }
}

This class uses the repository pattern to manage the model.

[TestClass]
public class ShipRepositoryTests
{
    [TestCleanup]
    public void Cleanup()
    {
        RunNonQuery("TRUNCATE TABLE Ships");
    }

    [TestMethod]
    public void GetListReturnsAllRows()
    {
        // Arrange
        RunNonQuery("INSERT INTO Ships (Name) VALUES ('Ship 1')");
        RunNonQuery("INSERT INTO Ships (Name) VALUES ('Ship 2')");
        var repository = new ShipRepository(GetConnectionString());

        // Act
        IEnumerable<Ship> ships = repository.GetList();

        // Assert
        Assert.AreEqual(2, ships.Count());
        Assert.IsTrue(ships.Select(s => s.Name).Contains("Ship 1"));
        Assert.IsTrue(ships.Select(s => s.Name).Contains("Ship 2"));
    }

    [TestMethod]
    public void InsertShipInsertsRow()
    {
        // Arrange
        var repository = new ShipRepository(GetConnectionString());

        // Act
        repository.Insert(new Ship { Name = "New Ship" });

        // Assert
        var shipNames = RunStringListQuery("SELECT Name FROM Ships");
        Assert.AreEqual(1, shipNames.Count());
        Assert.IsTrue(shipNames.Contains("New Ship"));
    }

    private string GetConnectionString()
    {
        return ConfigurationManager
            .ConnectionStrings["Test"].ToString();
    }

    private void RunNonQuery(string query)
    {
        using (var connection = new SqlConnection(GetConnectionString()))
        {
            connection.Open();
            using (var command = new SqlCommand(query, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    private IEnumerable<string> RunStringListQuery(string query)
    {
        var result = new List<string>();
        using (var connection = new SqlConnection(GetConnectionString()))
        {
            connection.Open();
            using (var command = new SqlCommand(query, connection))
            using(var reader = command.ExecuteReader())
            {
                while(reader.Read())
                {
                    result.Add(reader.GetString(0));
                }
            }
        }
        return result;
    }
}

These tests are run against a separate test database which is emptied after each test.

Testing the controller

The controller is the most straightforward part of ASP.NET MVC to test.

public class ShipController : Controller
{
    private readonly IShipRepository Repository;

    public ShipController() : this(new ShipRepository()) { }

    public ShipController(IShipRepository repository)
    {
        Repository = repository;
    }

    public ViewResult Index()
    {
        IEnumerable<Ship> ships = Repository.GetList();
        return View(ships);
    }

    public RedirectResult Create(Ship ship)
    {
        if (ModelState.IsValid)
        {
            Repository.Insert(ship);
        }
        return Redirect("/");
    }
}

The only thing this controller depends on is the repository which handles the model.

[TestClass]
public class ShipControllerTests
{
    [TestMethod]
    public void IndexReturnsAllShips()
    {
        var ships = new List<Ship>
            {
                new Ship { Name = "Ship 1"},
                new Ship { Name = "Ship 2"}
            };

        // Arrange
        var repository = Mock.Of<IShipRepository>(r => r.GetList() == ships);
        var controller = new ShipController(repository);

        // Act
        ViewResult result = controller.Index();

        // Assert
        Assert.AreEqual(ships, result.Model);
    }

    [TestMethod]
    public void CreateInsertsShip()
    {
        Ship ship = new Ship { Name = "New Ship" };

        // Arrange
        var repository = Mock.Of<IShipRepository>();
        var controller = new ShipController(repository);

        // Act
        controller.Create(ship);

        // Assert
        Mock.Get(repository).Verify(m => m.Insert(ship));
    }

    [TestMethod]
    public void CreateRedirectsToRoot()
    {
        Ship ship = new Ship { Name = "New Ship" };

        // Arrange
        var repository = Mock.Of<IShipRepository>();
        var controller = new ShipController(repository);

        // Act
        RedirectResult result = controller.Create(ship);

        // Assert
        Assert.AreEqual("/", result.Url);
    }
}

This is using Moq to replace the repository and the arrange-act-assert pattern to perform the tests.

SignalR: talk to your clients

In HTTP the browser is supposed to initiate all requests. This makes two way communication difficult. If the server wants to push data to the browser the browser has to poll for it. Possibly with the help of Server-sent events. There is a modern solution, however. It is called WebSocket. But even when it can be used all it provides is a low-level TCP connection.

SignalR takes care of both of these problems; It uses the best technology available and it provides a common easy-to-use interface regardless of the underlying connection. SignalR is intended to be used with ASP.NET and jQuery.

Counter: <span id="counter">-</span>
<button id="reset">Reset</button>

For this demonstration I want to have a counter that repeatedly counts up and a button to reset the it to zero. The magic is that the same number is to be displayed on all connected browsers.

<script src="~/Scripts/jquery.min.js"></script>
<script src="~/Scripts/jquery.signalR.min.js"></script>
<script src="~/signalr/hubs"></script>
[assembly: OwinStartup(typeof(SignalRTest.Startup))]

namespace SignalRTest
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            app.MapSignalR();
        }
    }
}

First there are some infrastructure on the browser and server that needs to be in place before SignalR can be used. The hubs script is created by SignalR, it does not exist physically.

$(function () {
    $.connection.counterHub.client.update =
        function (value) {
            $("#counter").html(value);
        };
    $("#reset").on("click", function () {
        $.connection.counterHub.server.reset();
    });
    $.connection.hub.start();
});

This is all the JavaScript necessary for this little demonstration. The first part defines an update function to be called from the server. The second part binds the button to a call to a reset method on the server.

public class CounterHub : Hub
{
    private static int value = 0;

    public void Reset()
    {
        Interlocked.Exchange(ref value, 0);
        Clients.All.writeTime(value);
    }

    public static void Increment()
    {
        Interlocked.Increment(ref value);
        IHubContext context = GlobalHost.ConnectionManager
            .GetHubContext<CounterHub>();
        context.Clients.All.update(value);
    }
}

On the server side is the reset method and a call to the update function. The update call is wrapped in a static method because it has to be called from outside the hub. This is the recommended way of doing it.

new Timer(o => CounterHub.Increment(), null, 0, 100);

I put this line in Application_Start in the Global.asax.cs file. It works for the demonstration. In a real scenario it should definitely be replaced with something more robust.

Unit testing: replacing dependencies

The point of unit testing is to test classes in isolation. This presents a problem when the class has dependencies. These dependencies must be replaced somehow.

namespace SpaceGame
{
    public class Level
    {
        private IEnumerable<IEnemy> enemies;

        public Level(params IEnemy[] enemies)
        {
            this.enemies = enemies;
        }

        public bool Cleared
        {
            get { return !enemies.Any(e => e.Alive); }
        }

        public void Shoot(int damage)
        {
            damage = damage / enemies.Count();
            foreach (var enemy in enemies)
            {
                enemy.Hit(damage);
            }
        }
    }
}

Dependency injection gives an opportunity to make those replacements. The dependencies are passed to the class instead of the class creating them itself. And by using interfaces the dependencies can be replaced with something else.

namespace SpaceGame.UnitTests
{
    [TestFixture]
    public class LevelTests
    {
        [Test]
        public void OneEnemyNotAlive_IsCleared()
        {
            var enemy = Substitute.For<IEnemy>();
            enemy.Alive.Returns(false);

            Level level = new Level(enemy);

            Assert.IsTrue(level.Cleared);
        }

        [Test]
        public void Shoot_TwoEnemies_DealsHalfDamageToEach()
        {
            var enemy1 = Substitute.For<IEnemy>();
            var enemy2 = Substitute.For<IEnemy>();

            Level level = new Level(enemy1, enemy2);
            level.Shoot(100);

            enemy1.Received().Hit(50);
            enemy2.Received().Hit(50);
        }
    }
}

Isolation frameworks provides a convenient way to create replacements for dependencies. The replacements can be configured to behave as needed for the test. They can also be used to check that they are called appropriately during the test. There are many isolation frameworks available for .NET. This example uses NSubstitute.

Unit testing: getting started

Unit testing is hailed by many as the best way to write and maintain code. Unit tests are written with the help of a unit testing framework. NUnit is the dominating framework in .NET.

namespace SpaceGame
{
    public class Enemy
    {
        private int hitpoints;

        public Enemy(int hitpoints)
        {
            this.hitpoints = hitpoints;
        }

        public bool Alive
        {
            get
            {
                return hitpoints > 0;
            }
        }

        public void Hit(int damage)
        {
            if (damage < 0)
            {
                throw new ArgumentOutOfRangeException("damage");
            }
            hitpoints -= damage;
        }
    }
}

In NUnit tests are contained in its own class. Normally with a test class for each class under test. It is also practical to put all tests in a separate project and assembly.

To run the tests we need a test runner. NUnit has its own runner in the form of a console or GUI program. However, it is much more convenient to use a runner that integrates with Visual Studio. If you already use ReSharper or CodeRush they might be a good choice.

namespace SpaceGame.UnitTests
{
    [TestFixture]
    public class EnemyTests
    {
        private Enemy enemy;

        [SetUp]
        public void Setup()
        {
            enemy = new Enemy(100);
        }

        [Test]
        public void NewEnemy_IsAlive()
        {
            Assert.IsTrue(enemy.Alive);
        }

        [TestCase(100)]
        [TestCase(101)]
        [TestCase(Int32.MaxValue)]
        public void Hit_OnceForMoreThanHitpoints_Kills(int damage)
        {
            enemy.Hit(damage);
            Assert.IsFalse(enemy.Alive);
        }

        [TestCase(0)]
        [TestCase(1)]
        [TestCase(50)]
        [TestCase(99)]
        public void Hit_OnceForLessThanHitpoints_RemainsAlive(int damage)
        {
            enemy.Hit(damage);
            Assert.IsTrue(enemy.Alive);
        }

        [TestCase(-1)]
        [TestCase(Int32.MinValue)]
        public void Hit_OnceForNegativeDamage_ThrowsException(int damage)
        {
            Assert.Catch<ArgumentOutOfRangeException>(() =>
            {
                enemy.Hit(damage);
            });
        }

        [Test]
        [Ignore("Currently fails due to bug 1")]
        public void Hit_TwiceForMaxInteger_Kills()
        {
            enemy.Hit(Int32.MaxValue);
            enemy.Hit(Int32.MaxValue);
            Assert.IsFalse(enemy.Alive);
        }
    }
}

An important goal when writing unit tests is to make them readable. Each test should test for one thing only and tests should not contain any logic of their own. Contrary to regular programming hardcoded values are a good thing when writing tests.

The last part shows how a test can be ignored which may be useful when a bug is uncovered but there isn’t time to fix it yet. This should be used sparingly of course.

Security in headers

Cross site scripting (XSS) attacks can be difficult to prevent when developing web sites. However, the browser can be recruited in this struggle by sending it the appropriate headers. This example shows how this might be setup in the web.config file of a simple HTTPS enabled ASP.NET site.

<configuration>
  <system.webServer>
    <httpProtocol>
      <customHeaders>
        <add name="Content-Security-Policy"
          value="default-src 'none'; img-src 'self'; style-src 'self'; script-src 'self'" />
        <add name="X-Frame-Options" value="deny" />
        <add name="X-XSS-Protection" value="1; mode=block"/>
        <add name="X-Content-Type-Options" value="nosniff" />
        <add name="Strict-Transport-Security" value="max-age=31536000" />
      </customHeaders>
    </httpProtocol>
  </system.webServer>
  <system.web>
    <httpCookies httpOnlyCookies="true" requireSSL="true" />
  </system.web>
</configuration>

Content-Security-Policy tells the browser where content should be allowed from. In this example only images, style sheets and script files from the same origin as the page are allowed. Everything else is blocked, including inline scripts and styles. Other content that can be controlled are fonts, media, plugins, connections and frames. Internet Explorer lacks support for this header.

X-Frame-Options provide the reverse control over frames. While Content-Security-Policy controls frames within the page this header controls the page within frames. In other words it can stop your page from being embedded inside someone else’s website.

X-XSS-Protection controls how Internet Explorer handles expected XSS attacks. This filter is already active by default but can be told to block pages completely instead of trying to sanitize them.

X-Content-Type-Options with its only valid option nosniff tells Internet Explorer to stop guessing MIME types and instead trust the server to set the content type correctly.

Strict-Transport-Security tells the browser that this host should be accessed via HTTPS. It is ignored when returned over HTTP. The normal use case for this header is to redirect users arriving via HTTP to HTTPS and return this header to tell the browser to connect directly via HTTPS in the future. This is not supported in Internet Explorer.

The last setting in the example tells ASP.NET to send cookies with the HttpOnly and Secure attributes. Together these tell the browser to only transmit the cookie over HTTPS and not expose it to client side scripts. Take care that you only access the server via HTTPS when using requireSSL because the ASP.NET session identifier will be recreated when the session cookie is not present in the request.

Trimming Handlebars with Razor

In JavaScript heavy web applications it is important to generate HTML in an organized way. This is an idea on how to accomplish this with the Handlebars template library, ASP.NET MVC and Razor views.

<div>
  <h2>{{name}}</h2>
  <ul>
    {{#each events}}
      <li>{{> event}}</li>
    {{/each}}
  </ul>
</div>
<strong>{{name}}</strong>

Each Handlebars template is put in a Razor file. By using Razor files we get some support from Visual Studio for writing the HTML. This also gives us access to the whole Razor engine, which could be used to handle localization among other things.

[OutputCache(Duration=604800)]
public ActionResult Compiled()
{
    string path = "~/Views/Templates/";

    var files = Directory.EnumerateFiles(Server.MapPath(path));
    var names = files.Select(f =>
        Path.GetFileNameWithoutExtension(f).ToLowerInvariant());

    var engine = new ScriptEngine();
    engine.ExecuteFile(Server.MapPath("~/Scripts/handlebars.js"));
    engine.Execute(@"var precompile = Handlebars.precompile;");

    var compiled = new StringBuilder(10240);
    compiled.Append("var templates = {");
    foreach (string name in names)
    {
        string file = path + name + ".cshtml";
        string template = RenderRazorViewToString(file);

        compiled.Append(name);
        compiled.Append(": Handlebars.template(");
        compiled.Append(engine.CallGlobalFunction("precompile", template));
        compiled.Append("),");
    }
    compiled.Append("};");

    foreach (string name in names)
    {
        compiled.AppendFormat(
            @"Handlebars.registerPartial(""{0}"", templates.{0});",
            name);
    }

    return Content(compiled.ToString(), "text/javascript");
}

Handlebars templates must be compiled before use. To increase performance we can compile them on the server, combine them and cache the result. To run Handlebars on the server we use Jurassic, an implementation of JavaScript for .NET. To get the templates from the Razor files see this implementation of RenderRazorViewToString.

The second foreach loop registers every template as a partial in order to use it from another template with the {{> name}} syntax. Only the templates used from other templates need to be registered but this just registers them all for simplicity.

<script src="~/Scripts/handlebars.runtime.js"></script>
<script src="~/Templates/Compiled"></script>

Since the templates are already compiled we only need the runtime version of Handlebars on the client. The precompiled templates are included like any other script.

(function () {
    var skiing = {
        name: 'Alpine',
        events: [
            { name: 'Downhill' },
            { name: 'Slalom' },
            { name: 'Super G' }
        ]
    };
    var body = document.getElementById('body');
    body.innerHTML = templates.sport(skiing);
})();

Using the templates is easy. Combine this system with a JavaScript framework and a REST resource and it could be something.

Organizing JavaScript with AMD

Asynchronous Module Definition is a standard for modularizing JavaScript code. RequireJS is one implementation of AMD.

define({
    pi: 3.14159,
    e: 2.71828
});

A module in AMD consists of a a value and an identifier. The identifier is the name of the file that contains the module minus the base URL and the file ending.

define(['app/constants'], function (constants) {
    return function (radius) {
        return {
            radius: radius,
            diameter: 2 * radius,
            circumference: 2 * constants.pi * radius,
            area: constants.pi * radius * radius
        };
    };
});

When a module is defined with a function that function will be called and the result will be used as the value. That value can still be a function which is useful because it can be used as a constructor.

This also shows how one module can depend other modules. AMD will take care of loading the other modules and pass their values to the function. Hence a function is required in this case.

require.config({
    baseUrl: '/Scripts',
    paths: {
        'jquery': 'http://code.jquery.com/jquery-2.1.0.min'
    }
});

require(['jquery', 'app/circle'], function ($, circle) {
    $('body').html(circle(5.3).area);
});

In the main JavaScript file RequireJS is configured and the application is started. Since this does not define a module it uses require instead of define.

<script src="/Scripts/lib/require.js" data-main="/Scripts/app/main"></script>

The only file that has to be included in the traditional way is the one for RequireJS. It then needs to know which file to start the application with.

The drawback of AMD is that there can only be one module per file. While this is good for organizing code during development it means a lot of files to download to the browser. AMD does load files asynchronously when needed. However, when used in production some way of combining JavaScript files is almost necessary.

Let the client do the work

Websites are increasingly built using Ajax and JavaScript. This has culminated in the single page application design. These are websites where the server is used only as a data store and all the heavy lifting is done in the client.

Writing this much client side code would be difficult without a good framework. Fortunately, there are many to choose from. Unfortunately, there are many to choose from. Most, if not all, of them are built on MV* principles, where the * stands for the fact that they often deviate from the MVC pattern.

Backbone.js

$(function () {
    var MainModel = Backbone.Model.extend({
        defaults: {
            greeting: 'Hello world'
        }
    });

    var MainView = Backbone.View.extend({
        tagName: 'h2',
        initialize: function () {
            this.model.on('change', this.render, this);
            this.render();
        },
        render: function () {
            this.$el.html(this.model.get('greeting'));
            $('body').html(this.$el);
        }
    });

    var AppRouter = Backbone.Router.extend({
        routes: {
            '': 'main'
        },
        main: function () {
            var model = new MainModel();
            var view = new MainView({ model: model });
            setTimeout(function () {
                model.set('greeting', 'Cruel world');
            }, 1700);
        }
    });

    new AppRouter();
    Backbone.history.start();
});

Backbone is one of the more popular frameworks. This example shows how models, views and routes are used to assemble the application. In addition there are collections which handle collections of models.

While some frameworks are very rigid in their structure Backbone is rather more flexible. There are some recommendations though. Use Underscore.js templates when rendering views. Use REST when getting data from the server. And you probably want to include jQuery even though the documentation likes to consider it optional.

There are plenty of extensions to Backbone. Most of which can be served from cdnjs together with Backbone itself and its dependencies.

SQL Server: aggregative queries

Quite often we need to aggregate results from many rows. Here are a couple of techniques for doing this in Transact-SQL, most of these queries will probably only work in SQL Server.

Id Name
1 Denmark
2 Finland
3 Iceland
4 Norway
5 Sweden

In order to have some data to play with I have made up some competition results between the five Nordic countries. The data is held in two tables; Nations (above) and Medals (below). For simplicity’s sake the medal values are stored as CHAR(6) with a CHECK constraint instead of adding another table.

Id NationId Value
1 1 Gold
2 1 Gold
3 1 Silver
4 1 Silver
5 1 Bronze
6 1 Bronze
7 1 Bronze
8 2 Gold
9 2 Gold
10 4 Gold
11 4 Gold
12 4 Bronze
13 4 Bronze
14 4 Bronze
15 5 Silver
16 5 Silver
17 5 Silver
18 5 Silver

GROUP BY

First of we have the standard GROUP BY clause. This trivial example counts the total amount of medals for each nation.

SELECT
    n.Name AS Nation,
    COUNT(m.Value) AS Medals
FROM
    Nations n
    LEFT JOIN Medals m ON m.NationId = n.Id
GROUP BY
    n.Name
ORDER BY
    Medals DESC,
    Nation
Nation Medals
Denmark 7
Norway 5
Sweden 4
Finland 2
Iceland 0

XML PATH

FOR XML is intended to be used to transform result sets into XML. But it can also be abused to concatenate text. For example a comma separated list of medals. Here the STUFF function takes care of the leading comma and the SUBSTRING call is a simple trick to sort the medal values in the natural order (Gold, Silver, Bronze).

SELECT
    n.Name AS Nation,
    COALESCE(STUFF((
        SELECT
            ', ' + CAST(COUNT(m.Value) AS VARCHAR(5))
                + ' ' + RTRIM(m.Value)
        FROM
            Medals m
        WHERE
            m.NationId = n.Id
        GROUP BY
            m.Value
        ORDER BY
            SUBSTRING(m.Value, 3, 2)
        FOR XML PATH('')
    ), 1, 2, ''), '') AS Medals
FROM
    Nations n
ORDER BY
    Nation
Nation Medals
Denmark 2 Gold, 2 Silver, 3 Bronze
Finland 2 Gold
Iceland
Norway 2 Gold, 3 Bronze
Sweden 4 Silver

PIVOT

Sometimes we may want to rotate rows to columns or columns to rows. This is kind of what the PIVOT and UNPIVOT operators in a FROM clause do. Here the result is a pretty useful medal table.

SELECT
    p.Nation,
    COALESCE(p.Gold, 0) AS Gold,
    COALESCE(p.Silver, 0) AS Silver,
    COALESCE(p.Bronze, 0) AS Bronze
FROM
    (SELECT
        n.Name AS Nation,
        m.Value
    FROM
        Nations n
        LEFT JOIN Medals m ON m.NationId = n.Id
    ) o
    PIVOT(
        COUNT(o.Value)
    FOR
        o.Value IN (Gold, Silver, Bronze)
    ) p
ORDER BY
    Gold DESC,
    Silver DESC,
    Bronze DESC,
    Nation
Nation Gold Silver Bronze
Denmark 2 2 3
Norway 2 0 3
Finland 2 0 0
Sweden 0 4 0
Iceland 0 0 0

GROUPING SETS

The GROUPING SETS, CUBE and ROLLUP operators are extensions to the GROUP BY clause that allows grouping by more than one set. This example uses it to include a total of all awarded medals per nation together with the totals for each value. The GROUPING function provides a way to distinguish between a NULLs that are the result of missing data and NULLs that are the result of grouping.

SELECT
    n.Name AS Nation,
    COUNT(m.Value) AS Quantity,
    COALESCE(m.Value, 'Total') AS Value
FROM
    Nations n
    LEFT JOIN Medals m ON m.NationId = n.Id
GROUP BY GROUPING SETS
    (
        (n.Name, m.Value),
        (n.Name)
    )
HAVING
    m.Value IS NOT NULL
    OR GROUPING(m.Value) = 1
ORDER BY
    Nation,
    COALESCE(SUBSTRING(m.Value, 3, 2), 'Z')
Nation Quantity Value
Denmark 2 Gold
Denmark 2 Silver
Denmark 3 Bronze
Denmark 7 Total
Finland 2 Gold
Finland 2 Total
Iceland 0 Total
Norway 2 Gold
Norway 3 Bronze
Norway 5 Total
Sweden 4 Silver
Sweden 4 Total

APPLY

CROSS APPLY and OUTER APPLY are similar to INNER JOIN and OUTER JOIN but with the difference that the left side is always evaluated first. This allows for some queries that are impossible otherwise or can give performance benefits over alternative queries. Here it is used to get the top 3 medals for each nation, something that may be complicated to do in other ways.

SELECT
    n.Name AS Nation,
    t.Value
FROM
    Nations n
    CROSS APPLY
    (SELECT
        TOP 3
        m.Value
    FROM
        Medals m
    WHERE
        m.NationId = n.Id
    ORDER BY
        SUBSTRING(m.Value, 3, 2)
    ) t
ORDER BY
    Nation
Nation Value
Denmark Gold
Denmark Gold
Denmark Silver
Finland Gold
Finland Gold
Norway Gold
Norway Gold
Norway Bronze
Sweden Silver
Sweden Silver
Sweden Silver

OVER

The OVER clause is used to apply a function over its own range. This would normally be a partitioned range for aggregative functions or an ordered range for ranking functions. In this case we use it to return the total amount of medals awarded in the competition and then calculate how many percent of the total each nation has earned.

SELECT
    n.Name AS Nation,
    CAST(ROUND(
        100.0
        * COUNT(m.Value)
        / SUM(COUNT(m.Value)) OVER ()
    , 0) AS INT) AS Percentage
FROM
    Nations n
    LEFT JOIN Medals m ON m.NationId = n.Id
GROUP BY
    n.Name
ORDER BY
    Percentage DESC,
    Nation
Nation Percentage
Denmark 39
Norway 28
Sweden 22
Finland 11
Iceland 0