constituency_counts (view)

43 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: seat_count, candidate_count, party_count, competition

constituency seat_count candidate_count party_count competition
Kildare South 4 1 1 0.25
Dún Laoghaire 4 11 10 2.75
Dublin South-West 5 16 10 3.20
Dublin West 5 16 10 3.20
Kildare North 5 16 12 3.20
Mayo 5 16 8 3.20
Wicklow–Wexford 3 10 8 3.33
Dublin Mid-West 5 17 12 3.40
Galway West 5 17 12 3.40
Kerry 5 17 10 3.40
Dublin Central 4 14 11 3.50
Galway East 4 14 9 3.50
Cork North-West 3 11 8 3.67
Offaly 3 11 8 3.67
Roscommon–Galway 3 11 9 3.67
Dublin Rathdown 4 15 10 3.75
Wexford 4 15 11 3.75
Longford–Westmeath 5 19 10 3.80
Carlow–Kilkenny 5 20 11 4.00
Cavan–Monaghan 5 20 10 4.00
Cork North-Central 5 20 11 4.00
Dublin Bay North 5 20 10 4.00
Dublin Fingal West 3 12 11 4.00
Laois 3 12 7 4.00
Tipperary South 3 12 10 4.00
Waterford 4 16 10 4.00
Cork South-Central 5 21 12 4.20
Donegal 5 21 11 4.20
Limerick City 4 17 11 4.25
Sligo–Leitrim 4 17 11 4.25
Dublin North-West 3 13 11 4.33
Meath West 3 13 12 4.33
Dublin Bay South 4 18 10 4.50
Dublin South-Central 4 18 14 4.50
Meath East 4 18 11 4.50
Wicklow 4 18 10 4.50
Cork South-West 3 14 11 4.67
Tipperary North 3 14 9 4.67
Cork East 4 19 11 4.75
Louth 5 24 12 4.80
Clare 4 20 12 5.00
Dublin Fingal East 3 15 12 5.00
Limerick County 3 15 11 5.00

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW constituency_counts AS WITH candidate_counts AS
  (SELECT constituencies.seat_count AS seat_count,
          constituencies.constituency AS constituency,
          count(candidates.name) AS candidate_count,
          candidates.party AS party
   FROM constituencies
   JOIN candidates ON constituencies.constituency = candidates.constituency
   GROUP BY constituencies.constituency,
            candidates.party
   ORDER BY constituencies.constituency ASC)
SELECT constituency,
       seat_count,
       sum(candidate_count) AS candidate_count,
       count(candidate_count) AS party_count,
       printf("%.2f", sum(candidate_count)*1.0 / seat_count) AS competition
FROM candidate_counts
GROUP BY constituency
ORDER BY competition ASC;