Handy SQL statements

Handy SQL statements

The following SQL statements are useful for checking imported data and looking for possible error conditions in imported data:

SELECT * FROM pollingstations_pollingdistrict
WHERE polling_station_id NOT IN
    (SELECT internal_council_id FROM pollingstations_pollingstation
     WHERE council_id='X01000000')
AND council_id='X01000000';

SELECT * FROM pollingstations_pollingstation
WHERE polling_district_id NOT IN
    (SELECT internal_council_id FROM pollingstations_pollingdistrict
     WHERE council_id='X01000000')
AND council_id='X01000000';

SELECT DISTINCT(polling_station_id), COUNT(*)
FROM pollingstations_pollingdistrict
WHERE council_id='X01000000'
GROUP BY polling_station_id
HAVING COUNT(*)>1
ORDER BY COUNT(*);

SELECT DISTINCT(polling_district_id), COUNT(*)
FROM pollingstations_pollingstation
WHERE council_id='X01000000'
GROUP BY polling_district_id
HAVING COUNT(*)>1
ORDER BY COUNT(*);

SELECT * FROM pollingstations_pollingstation ps
JOIN pollingstations_pollingdistrict pd
ON ST_Contains(pd.area, ps.location)
WHERE pd.council_id='X01000000' AND ps.council_id='X01000000';

SELECT DISTINCT(postcode), COUNT(DISTINCT(polling_station_id))
FROM pollingstations_residentialaddress
WHERE council_id='X01000000'
GROUP BY postcode
HAVING COUNT(DISTINCT(polling_station_id))>1;