title: Dynamic fieldnames in subquery?
body: <p>I have a table similar to the following:</p>
  
  <pre><code>CREATE TABLE stats (
    name character varying(15),
    q001001 numeric(9,0),
    q001002 numeric(9,0),
    q001003 numeric(9,0),
    q001004 numeric(9,0),
    q001005 numeric(9,0)
  )
  </code></pre>
  
  <p>I need to query this table for the sums of various fields within, like this:</p>
  
  <pre><code>SELECT sum(q001001) as total001,
         sum(q001002) as total002,
         sum(q001005) as total005,
  FROM stats;
  </code></pre>
  
  <p>This produces a result with ONE row of data, and THREE columns.<br>
  However, I need the results to be listed the other way around, for reporting purposes. I need THREE rows and ONE column (well, two actually, the first being the field that was sum'd) like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>FieldName | SUM
  ----------+-------
  q001001   |  12345
  q001002   |  5432
  q001005   |  986
  </code></pre>
  
  <p>I'd like to use some SQL like this, where the <code>field_name</code> (from a lookup table of the field names in the stats table) is used in a sub-query:</p>
  
  <pre><code>select l.field_name, (select sum(l.field_name) from stats)
  from stats_field_names_lookup as l
  where l.field_name in ('Q001001', 'Q001002', 'Q001005');
  </code></pre>
  
  <p>The thinking here is that <code>sum(l.field_name)</code> would be replaced by the actual field name in question, for each of those in the <code>WHERE</code> clause, and then evaluated to provide the correct sum'd result value. This, however, fails with the following error:</p>
  
  <blockquote>
    <p>function sum(character varying) does not exist</p>
  </blockquote>
  
  <p>because the value there is a text/character. How can I cast that character value to an unquoted string to be evaluated properly?</p>
  
  <p>This SQL works. But, of course, gives the same sum'd values for each <code>field_name</code>, since it is hard coded as <code>q001001</code> here.</p>
  
  <pre><code>select l.field_name, (select sum(q001001) from stats)
  from stats_field_names_lookup as l
  where l.field_name in ('Q001001', 'Q001002', 'Q001005');
  </code></pre>
  
  <p>So, I think the idea is sound in theory. Just need help figuring out how to get that character/string to be understood as a field_name. Anyone have any ideas?</p>