title: Calculate a weighted average for several columns
body: <p>I have a table, <code>table1</code>, containing multiple rows within each value of the column <code>ID</code>. I would like to calculate the same sort of weighed average using the same duration column <code>time</code> for a large number of columns.</p>
  
  <p>I can manually define each column:</p>
  
  <pre><code>drop table if exists table2;
  create table table2 as 
  select ID, 
   sum(var1*time)/sum(time) as var1,
   sum(var2*time)/sum(time) as var2,
   sum(var3*time)/sum(time) as var3,
   sum(var4*time)/sum(time) as var4
   from table1 group by ID;
  </code></pre>
  
  <p>Is there a way to do this programmatically for a large number of columns?</p>
  
  <p>Let's say I can use regex to identify the columns of interest. We can use the syntax var1, var2, var3 etc for this example.</p>