Get the number of times the text on a sign occurs, as used here: http://cdb.io/15ekzYE

signcount.sql

UPDATE sign_application_filings s1
SET text_occurrences = (
    SELECT COUNT(DISTINCT(s2.house, s2.street_name)) 
    FROM sign_application_filings s2
    WHERE s2.text_on_sign = s1.text_on_sign
)
WHERE text_on_sign != ''