title: Count number of farming households per district
body: <p>I have census data in the following columns:</p>
  
  <p>FIRST_NAME, SURNAME, OCCUPATION, HOUSEHOLD_ID, DISTRICT, COUNTY</p>
  
  <p>And some sample data:</p>
  
  <pre><code>Anne, Boyle, Student, 7090989, Green Acres, Coolidge 
  John, Boyle, Farmer, 7090989, Green Acres, Coolidge  
  Mary, Boyle, Farmer's wife, 7090989, Green Acres, Coolidge  
  James, Egan, Carpenter, 1090933, Green Acres, Coolidge
  Margret, Egan, Teacher, 1090933, Green Acres, Coolidge
  William, Dunne, Merchant, 2090921, Falls, Cleveland
  Anne, Smyth, Widow, 1110166, Range, Cleveland
  Daniel, McCann, Farm labourer, 1011112, Griffin, Cleveland
  Timothy, McCann, Farm labourer, 1011112, Griffin, Cleveland
  Patrick, Sullivan, Farmer, 2217733, Ards, Cleveland
  Maggie, Sullivan, Farmer, 2217733, Ards, Cleveland
  Michael, Petterson, Farmer labourer, 2227229, Ards, Cleveland
  </code></pre>
  
  <p>So I wish to count the number of farming households per district. Each household has a unique id and a single farming household is defined as a household with at least one occupant with an occupation <code>LIKE "%Farm%"</code>. So the number of farmers in the household is not of interest, just whether there is at least one farmer. For the data above I should get the following result:</p>
  
  <pre><code>Green Acres, Coolidge, 1
  Griffin, Cleveland, 1
  Ards, Cleveland, 2
  </code></pre>
  
  <p>This is how I establish the number of farmers per district:</p>
  
  <pre><code>Select DISTRICT, COUNTY, COUNT(*) from census where OCCUPATION like "%Farm%" group by DISTRICT, COUNTY;   
  </code></pre>
  
  <p>To get the number of households per district I do:</p>
  
  <pre><code>select DISTRICT, COUNTY, COUNT(distinct(HOUSEHOLD_ID)) from census group by DISTRICT, COUNTY;
  </code></pre>
  
  <p>So how do I count the number of farming households per district.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <pre><code>A county is made up of uniquely named districts.
  </code></pre>