Frequency of "Foo Township, Bar County, State" titles

This query is marked as a draft This query has been published by Certes.

Frequency is the number of townships sharing a name in different counties of the same state.  Cases is the number of town+state combinations with that frequency.  Townships is their product.  To interpret one row: 74 town+state combinations occur exactly five times, covering 370 townships – for example, five counties of Iowa each have an Adams Township.   The final line shows grand totals of cases and townships.

SQL

WITH Counts AS (
SELECT REGEXP_REPLACE(page_title, ",_.*_County", "") AS TownState, COUNT(*) AS Frequency
FROM page
LEFT JOIN page_props ON pp_page=page_id AND pp_propname="disambiguation"
WHERE page_namespace=0 AND page_is_redirect=0 AND page_title LIKE "%_Township,_%" AND pp_page IS NULL
GROUP BY TownState)
SELECT Frequency, COUNT(*) AS Cases, Frequency * COUNT(*) AS Townships
FROM Counts
GROUP BY Frequency WITH ROLLUP

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...