title: Count the number of rows that contain a letter/number
body: <p>What I am trying to achieve is straightforward, however it is a little difficult to explain and I don't know if it is actually even possible in postgres. I am at a fairly basic level. <code>SELECT, FROM, WHERE, LEFT JOIN ON, HAVING</code>, e.t.c the basic stuff.</p>
  
  <p>I am trying to count the number of rows that contain a particular letter/number and display that count against the letter/number.</p>
  
  <p>i.e How many rows have entries that contain an "a/A" (Case insensitive)</p>
  
  <p>The table I'm querying is a list of film names. All I want to do is group and count 'a-z' and '0-9' and output the totals. I could run 36 queries sequentially:</p>
  
  <pre><code>SELECT filmname FROM films WHERE filmname ilike '%a%'
  SELECT filmname FROM films WHERE filmname ilike '%b%'
  SELECT filmname FROM films WHERE filmname ilike '%c%'
  </code></pre>
  
  <p>And then run pg_num_rows on the result to find the number I require, and so on.</p>
  
  <p>I know how intensive like is and ilike even more so I would prefer to avoid that. Although the data (below) has upper and lower case in the data, I want the result sets to be case insensitive. i.e "The Men Who Stare At Goats" the a/A,t/T and s/S wouldn't count twice for the resultset. I can duplicate the table to a secondary working table with the data all being strtolower and working on that set of data for the query if it makes the query simpler or easier to construct.</p>
  
  <p>An alternative could be something like <br /><br />
  <code>SELECT sum(length(regexp_replace(filmname, '[^X|^x]', '', 'g'))) FROM films;</code><br /><br />
  for each letter combination but again 36 queries, 36 datasets, I would prefer if I could get the data in a single query.</p>
  
  <p>Here is a short data set of 14 films from my set (which actually contains 275 rows)</p>
  
  <pre><code>District 9
  Surrogates
  The Invention Of Lying
  Pandorum
  UP
  The Soloist
  Cloudy With A Chance Of Meatballs
  The Imaginarium of Doctor Parnassus
  Cirque du Freak: The Vampires Assistant
  Zombieland
  9
  The Men Who Stare At Goats
  A Christmas Carol
  Paranormal Activity
  </code></pre>
  
  <p>If I manually lay out each letter and number in a column and then register if that letter appears in the film title by giving it an x in that column and then count them up to produce a total I would have something like this below. Each vertical column of x's is a list of the letters in that filmname regardless of how many times that letter appears or its case.</p>
  
  <p>The result for the short set above is:</p>
  
  <pre><code>A  x x  xxxx xxx  9 
  B       x  x      2 
  C x     xxx   xx  6
  D x  x  xxxx      6
  E  xx  xxxxx x    8
  F   x   xxx       4 
  G  xx    x   x    4
  H   x  xxxx  xx   7
  I x x  xxxxx  xx  9
  J                 0
  K         x       0
  L   x  xx  x  xx  6
  M    x  xxxx xxx  8
  N   xx  xxxx x x  8
  O  xxx xxx x xxx  10
  P    xx  xx    x  5
  Q         x       1
  R xx x   xx  xxx  7
  S xx   xxxx  xx   8
  T xxx  xxxx  xxx  10
  U  x xx xxx       6
  V   x     x    x  3
  W       x    x    2
  X                 0 
  Y   x   x      x  3
  Z          x      1 
  0                 0  
  1                 0  
  2                 0 
  3                 0
  4                 0
  5                 0
  6                 0
  7                 0
  8                 0
  9 x         x     1
  </code></pre>
  
  <p>In the example above, each column is a "filmname" As you can see, column 5 marks only a "u" and a "p" and column 11 marks only a "9". The final column is the tally for each letter.</p>
  
  <p>I want to build a query somehow that gives me the result rows:  A 9, B 2, C 6, D 6, E 8 e.t.c taking into account every row entry extracted from my films column. If that letter doesn't appear in any row I would like a zero.</p>
  
  <p>I don't know if this is even possible or whether to do it systematically in php with 36 queries is the only possibility.</p>
  
  <p>In the current dataset there are 275 entries and it grows by around 8.33 a month (100 a year). I predict it will reach around 1000 rows by 2019 by which time I will be no doubt using a completely different system so I don't need to worry about working with a huge dataset  to trawl through.</p>
  
  <p>The current longest title is "Percy Jackson &amp; the Olympians: The Lightning Thief" at 50 chars (yes, poor film I know ;-) and the shortest is 1, "9".</p>
  
  <p>I am running version 9.0.0 of Postgres.</p>
  
  <p>Apologies if I've said the same thing multiple times in multiple ways, I am trying to get as much information out so you know what I am trying to achieve.</p>
  
  <p>If you need any clarification or larger datasets to test with please just ask and I'll edit as needs be.</p>
  
  <p>Suggestion are VERY welcome.</p>
  
  <p><em>Edit 1</em></p>
  
  <p><strong>Erwin</strong> Thanks for the edits/tags/suggestions. Agree with them all.</p>
  
  <p>Fixed the missing "9" typo as suggested by <strong>Erwin</strong>. Manual transcribe error on my part.<br /><br />
  <strong>kgrittn</strong>, Thanks for the suggestion but I am not able to update the version from 9.0.0. I have asked my provider if they will try to update.</p>
  
  <p><em>Response</em></p>
  
  <p>Thanks for the excellent reply <strong>Erwin</strong></p>
  
  <p>Apologies for the delay in responding but I have been trying to get your query to work and learning the new keywords to understand the query you created.</p>
  
  <p>I adjusted the query to adapt into my table structure but the result set was not as expected (all zeros) so I copied your lines directly and had the same result.</p>
  
  <p>Whilst the result set in both cases lists all 36 rows with the appropriate letters/numbers however all the rows shows zero as the count (ct).</p>
  
  <p>I have tried to deconstruct the query to see where it may be falling over.</p>
  
  <p>The result of <br /></p>
  
  <pre><code>SELECT DISTINCT id, unnest(string_to_array(lower(film), NULL)) AS letter
  FROM  films
  </code></pre>
  
  <p><br /> 
  is "No rows found". Perhaps it ought to when extracted from the wider query, I'm not sure.</p>
  
  <p>When I removed the unnest function the result was 14 rows all with "NULL"</p>
  
  <p>If I adjust the function<br /></p>
  
  <pre><code>COALESCE(y.ct, 0) to COALESCE(y.ct, 4)&lt;br /&gt;
  </code></pre>
  
  <p>then my dataset responds all with 4's for every letter instead of zeros as explained previously.</p>
  
  <p>Having briefly read up on COALESCE the "4" being the substitute value I am guessing that y.ct is NULL and being substituted with this second value (this is to cover rows where the letter in the sequence is not matched, i.e if no films contain a 'q' then the 'q' column will have a zero value rather than NULL?)</p>
  
  <p>The database I tried this on was SQL_ASCII and I wondered if that was somehow a problem but I had the same result on one running version 8.4.0 with UTF-8.</p>
  
  <p>Apologies if I've made an obvious mistake but I am unable to return the dataset I require.</p>
  
  <p>Any thoughts?</p>
  
  <p>Again, thanks for the detailed response and your explanations.</p>