2015 UK General Election using SQL Server

This tutorial assumes you have access to powershell and sqlcmd or Microsoft SQL Server Management Studio

Get to the powershell prompt and download the csv

Invoke-WebRequest http://researchbriefings.files.parliament.uk/documents/CBP-7979/hocl-ge2017-results-full.csv -OutFile ge2017.csv

Go into sqlcmd or SSMS and create a table for the results

Here you create a single flat table that can store all of the unnormalised data.

The first line of the CSV file contains the column headings (ons_id,ons_region_id,constituency_name,county_name,region_name,country_name,constituency_type,party_name,party_abbreviation,firstname,surname,gender,sitting_mp,former_mp,votes,share,change). We create a table with a column for each of these:

sqlcmd -S .\sqlexpress -E

We do not know the size of each column yet, too small and the import statement will give errors or warnings. It is best to be generous - memory is cheap.

The create table statement could be:

CREATE TABLE ge(
  ons_id VARCHAR(10),
  ons_region_id VARCHAR(10),
  constituency_name VARCHAR(50),
  county_name VARCHAR(50),
  region_name VARCHAR(50),
  country_name VARCHAR(50),
  constituency_type VARCHAR(10),
  party_name VARCHAR(50),
  party_abbreviation VARCHAR(50),
  firstname VARCHAR(50),
  surname VARCHAR(50),
  gender VARCHAR(6),
  sitting_mp VARCHAR(3),
  former_mp VARCHAR(3),
  votes INT,
  share FLOAT,
  change VARCHAR(20),
  PRIMARY KEY(ons_id,firstname,surname)
)

Attempt import the csv file into your flat table

You can import the data using this line (from SQLCMD or SQL Server Management Studio)

BULK INSERT ge FROM 'C:\db\ge2017.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)
GO

• Each line is ended with carriage return \n
• The first row contains column headings not data so we start at row 2

You will most likely get error messages like this:

Msg 4863, Level 16, State 1, Server ME1C039-130368\SQLEXPRESS, Line 3
Bulk load data conversion error (truncation) for row 105, column 13 (sitting_mp).
Msg 4863, Level 16, State 1, Server ME1C039-130368\SQLEXPRESS, Line 3
Bulk load data conversion error (truncation) for row 106, column 12 (gender).
Msg 4863, Level 16, State 1, Server ME1C039-130368\SQLEXPRESS, Line 3
Bulk load data conversion error (truncation) for row 107, column 13 (sitting_mp).
...

Unfortunately BULK INSERT cannot deal with the CSV format so we need to pre-process the file. Some solutions:

• Use Excel - you can load CSV and save as TXT
• Use regular expressions in powershell: convert CSV to TXT
• Use python
• Find a converter online

With the conversion complete you can import the data - you will have too use the full path name and you may have permissions problems

BULK INSERT ge FROM 'c:\path\ge2017.txt' WITH (FIRSTROW=2)

You may get a few errors - it is probably easiest to fix these "by hand" using Excel or a text editor. The BULK INSERT command should show you the line numbers. Check that you have the correct number of rows - there should be 3304 data rows.

SELECT COUNT(*) FROM ge

Run some queries

Now let's look at some data. How many female candidates were there?

select count(1) from ge where gender='female';

Who stood in Edinburgh South?

select surname,votes from ge where constituency_name='Edinburgh South';

Who won the election?

The important question is which party got the most seats. Which party leader should the queen invite to form a government? We can use the RANK function for this but we prefer to use a slower, simpler solution:

First calculate the max votes per constituency:

SELECT ons_id,MAX(votes) AS mv
  FROM ge
GROUP BY ons_id

Now JOIN that to the original table to find the party that won each seat:

SELECT ge.ons_id, ge.party_name
  FROM ge JOIN
       (SELECT ons_id,MAX(votes) AS mv
          FROM ge
        GROUP BY ons_id) AS ms ON ge.ons_id=ms.ons_id AND ge.votes=ms.mv

We can now count the seats by party:

SELECT party_name,COUNT(1) TotSeat
  FROM (
    SELECT ge.ons_id, ge.party_name
      FROM ge JOIN
          (SELECT ons_id,MAX(votes) AS mv
             FROM ge
           GROUP BY ons_id) AS ms ON ge.ons_id=ms.ons_id AND ge.votes=ms.mv
       ) AS mp
GROUP BY party_name

You can now move to the next stage: 2015 UK General Election Normalising Data