# Difference between revisions of "Window functions"

Line 2: | Line 2: | ||

The candidate who gains the most votes becomes MP for that constituency. | The candidate who gains the most votes becomes MP for that constituency. | ||

+ | All these results are recorded in a table '''ge''' | ||

+ | <table><tr><th>yr</th><th>firstName</th><th>lastName</th><th>constituency</th><th>party</th><th>votes</th></tr><tr><td class="r">2015</td><td>Colin</td><td>Fox</td><td>S14000024</td><td>Scottish Socialist Party</td><td class="r">197</td></tr><tr><td class="r">2015</td><td>Ian</td><td>Murray</td><td>S14000024</td><td>Labour</td><td class="r">19293</td></tr><tr><td class="r">2015</td><td>Miles</td><td>Briggs</td><td>S14000024</td><td>Conservative</td><td class="r">8626</td></tr><tr><td class="r">2015</td><td>Neil</td><td>Hay</td><td>S14000024</td><td>Scottish National Party</td><td class="r">16656</td></tr><tr><td class="r">2015</td><td>Paul</td><td>Marshall</td><td>S14000024</td><td>UK Independence Party</td><td class="r">601</td></tr><tr><td class="r">2015</td><td>Phyl</td><td>Meyer</td><td>S14000024</td><td>Green</td><td class="r">2090</td></tr><tr><td class="r">2015</td><td>Pramod</td><td>Subbaraman</td><td>S14000024</td><td>Liberal Democrat</td><td class="r">1823</td></tr><tr><td class="r">2017</td><td>Alan Christopher</td><td>BEAL</td><td>S14000024</td><td>Liberal Democrats</td><td class="r">1388</td></tr><tr><td class="r">2017</td><td>Ian</td><td>MURRAY</td><td>S14000024</td><td>Labour</td><td class="r">26269</td></tr><tr><td class="r">2017</td><td>Jim</td><td>EADIE</td><td>S14000024</td><td>SNP</td><td class="r">10755</td></tr><tr><td class="r">2017</td><td>Stephanie Jane Harley</td><td>SMITH</td><td>S14000024</td><td>Conservative</td><td class="r">9428</td></tr></table> | ||

==Warming up== | ==Warming up== | ||

<div class='qu'> | <div class='qu'> |

## Revision as of 10:05, 6 January 2020

General Elections were held in the UK in 2015 and 2017. Every citizen votes in a **constituency**.
The candidate who gains the most votes becomes MP for that constituency.

All these results are recorded in a table **ge**

yr | firstName | lastName | constituency | party | votes |
---|---|---|---|---|---|

2015 | Colin | Fox | S14000024 | Scottish Socialist Party | 197 |

2015 | Ian | Murray | S14000024 | Labour | 19293 |

2015 | Miles | Briggs | S14000024 | Conservative | 8626 |

2015 | Neil | Hay | S14000024 | Scottish National Party | 16656 |

2015 | Paul | Marshall | S14000024 | UK Independence Party | 601 |

2015 | Phyl | Meyer | S14000024 | Green | 2090 |

2015 | Pramod | Subbaraman | S14000024 | Liberal Democrat | 1823 |

2017 | Alan Christopher | BEAL | S14000024 | Liberal Democrats | 1388 |

2017 | Ian | MURRAY | S14000024 | Labour | 26269 |

2017 | Jim | EADIE | S14000024 | SNP | 10755 |

2017 | Stephanie Jane Harley | SMITH | S14000024 | Conservative | 9428 |

## Contents

## Warming up

Show the **lastName**, **party** and **votes** for the **constituency** 'S14000024' in 2017.

```
SELECT lastName, party, votes
FROM ge
WHERE constituency = 'E14000539' AND yr = 2017
ORDER BY votes DESC
```

```
SELECT lastName, party, votes
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC
```

## Who won?

You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.

```
SELECT party, votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'E14000539' AND yr = 2017
ORDER BY votes
```

```
SELECT party, votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party
```

## PARTITION BY

The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.

```
SELECT yr,party, votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr
```

```
SELECT yr,party, votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr
```

## Edinburgh Constituency

Edinburgh constituencies are numbered S14000021 to S14000026.

```
SELECT constituency,party, votes
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,votes DESC
```

```
SELECT constituency,party, votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC)
AS posn
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY posn,constituency
```

## Winners Only

You can use SELECT within SELECT to pick out only the winners in Edinburgh.

```
SELECT constituency,party, votes
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,votes DESC
```

```
SELECT constituency, party
FROM (
SELECT constituency,party,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC)
AS posn
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
) AS ed
WHERE posn = 1
```

## Scottish seats

You can use **COUNT** and **GROUP BY** to see how each party did in Scotland. Scottish constituencies start with 'S'

```
SELECT constituency,party, votes
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,votes DESC
```

```
SELECT party,COUNT(1)
FROM (
SELECT constituency,party,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC)
AS posn
FROM ge
WHERE constituency LIKE 'S%'
AND yr = 2017
) AS ed
WHERE posn = 1
GROUP BY party
```