view_election (view)

Data source: ParlGov · About: simonw/parlgov-datasette

8,947 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE VIEW view_election AS

SELECT
  c.name_short AS country_name_short,
  c.name AS country_name,
  ii.short AS election_type,
  e.date AS election_date,
  vote_share, seats, seats_total,
  p.name_short AS party_name_short,
  p.name AS party_name,
  p.name_english AS party_name_english,
  vpp.left_right AS left_right,
  e.country_id AS country_id,
  er.election_id AS election_id,
  e.previous_parliament_election_id
    AS previous_parliament_election_id,
  e.previous_cabinet_id AS previous_cabinet_id,
  er.party_id AS party_id

FROM (SELECT * FROM election_result
               WHERE vote_share NOT NULL
                 OR seats NOT NULL) er

LEFT OUTER JOIN election e
    ON er.election_id = e.id
LEFT OUTER JOIN party p
    ON er.party_id = p.id
LEFT OUTER JOIN viewcalc_party_position AS vpp
    ON er.party_id = vpp.party_id
LEFT OUTER JOIN country c
    ON e.country_id = c.id
LEFT OUTER JOIN info_id ii
    ON e.type_id = ii.id

ORDER BY c.name_short, e.date, er.seats DESC;