title: SQL Server: weighted average + GROUP BY
body: <p>I am trying to calculate a <strong>weighted average</strong> in SQL Server. I'm aware that there are tons of questions out there addressing the problem, but I have the additional problem that I query a lot of other columns with a <strong>GROUP BY</strong> and <strong>aggregate functions</strong> like sum() and avg().</p>
  
  <p>Here is my query:</p>
  
  <pre><code>SELECT 
      AVG(tauftr.kalkek) AS 'PurchPrice',
      SUM(tauftr.amount) AS 'Amount',
      AVG(tauftr.price) AS 'SellingPrice',
      tauftr.product AS 'Product',
      auftrkopf.ins_usr AS 'Seller',
      DATEPART(wk, auftrkopf.date) AS 'Week',
      AVG([margin]) AS 'Margin' /* &lt;--- THIS IS WRONG  */ 
      /* CALCULATE HERE: WEIGHTED AVERAGE BETWEEN 'amount' and 'margin' */
  FROM 
      [tauftr] AS tauftr
  JOIN
      tauftrkopf AS auftrkopf ON tauftr.linktauftrkopf = auftrkopf.kopfnr
  WHERE
      auftrkopf.[status] = 'L'
      AND auftrkopf.typ = 'B'
      AND auftrkopf.date &gt;= '01.03.2017'
      AND auftrkopf.ins_usr ='HS'
  GROUP BY
      tauftr.product, auftrkopf.ins_usr, DATEPART(wk,auftrkopf.date)
  </code></pre>
  
  <p>I suppose it could be possible to use a <code>INNER JOIN</code> with exactly the same <code>WHERE</code> clause, but I don't want to execute the query two times. And I don't know ON what field to JOIN...</p>
  
  <p>Is it possible without creating a table? (I do not have write permissions)</p>