query Command

ubu-block --config <CONFIG_FILE> query -q "<SQL_QUERY>"

-- Results table (main data)
results (
  id INTEGER PRIMARY KEY,
  station_id INTEGER,
  candidate_id INTEGER,
  votes INTEGER,
  block_height INTEGER
)

-- Stations table
stations (
  id INTEGER PRIMARY KEY,
  name TEXT,
  ward_code TEXT
)

-- Candidates table
candidates (
  id INTEGER PRIMARY KEY,
  name TEXT,
  party_id INTEGER
)

ubu-block --config config.toml query -q "SELECT * FROM results"

ubu-block --config config.toml query -q "SELECT
  c.name as candidate,
  SUM(votes) as votes,
  ward_name as ward,
  constituency_name as constituency,
  county_name as county,
  parties.title as party
FROM
  results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN candidates c ON c.id = results.candidate_id
  INNER JOIN wards on stations.ward_code = wards.ward_code
  INNER JOIN parties ON parties.id = c.party_id
  INNER JOIN constituencies ON wards.constituency_code = constituencies.constituency_code
  INNER JOIN counties ON constituencies.county_code = counties.county_code
WHERE
  position_type = 'Mp' and constituency = 'Juja'
GROUP BY candidate"

+--------+--------------+----------+-----------+-------+-------+
| county | constituency |   ward   | candidate | party | votes |
+--------+--------------+----------+-----------+-------+-------+
| Kiambu |     Juja     | Kalimoni |   Omosh   |  ODM  |  21   |
| Kiambu |     Juja     | Kalimoni |   Mwas    |  PNU  |  66   |
+--------+--------------+----------+-----------+-------+-------+

ubu-block --config config.toml query -q "SELECT COUNT(*) as total_blocks FROM blocks"

ubu-block --config config.toml query -q "SELECT 
  station_id,
  candidate_id,
  votes,
  block_height
FROM results
ORDER BY block_height DESC
LIMIT 10"

ubu-block --config config.toml query -q "SELECT
  county_name,
  COUNT(DISTINCT station_id) as stations_reporting,
  SUM(votes) as total_votes
FROM results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN wards ON stations.ward_code = wards.ward_code
  INNER JOIN constituencies ON wards.constituency_code = constituencies.constituency_code
  INNER JOIN counties ON constituencies.county_code = counties.county_code
GROUP BY county_name
ORDER BY total_votes DESC"

ubu-block --config config.toml query -q "SELECT
  ward_name,
  c.name as candidate,
  SUM(votes) as votes
FROM results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN candidates c ON c.id = results.candidate_id
  INNER JOIN wards ON stations.ward_code = wards.ward_code
WHERE c.id = 1
GROUP BY ward_name
ORDER BY votes DESC"

SELECT 
  b.height,
  b.timestamp,
  COUNT(r.id) as results_in_block,
  SUM(r.votes) as total_votes
FROM blocks b
  LEFT JOIN results r ON r.block_height = b.height
GROUP BY b.height
ORDER BY b.height

SELECT 
  station_id,
  candidate_id,
  COUNT(*) as submission_count
FROM results
GROUP BY station_id, candidate_id
HAVING submission_count > 1

SELECT
  COUNT(*) as total_blocks,
  MIN(height) as first_block,
  MAX(height) as latest_block,
  (SELECT COUNT(DISTINCT station_id) FROM results) as stations_reported,
  (SELECT SUM(votes) FROM results) as total_votes_cast
FROM blocks

Error: Could not query
Caused by: near "FORM": syntax error

Error: no such table: result

Error: no such column: vote