SQL Server: weighted average + GROUP BY

Question

I am trying to calculate a weighted average 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 GROUP BY and aggregate functions like sum() and avg().

Here is my query:

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' /* <--- 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 >= '01.03.2017'
    AND auftrkopf.ins_usr ='HS'
GROUP BY
    tauftr.product, auftrkopf.ins_usr, DATEPART(wk,auftrkopf.date)

I suppose it could be possible to use a INNER JOIN with exactly the same WHERE clause, but I don't want to execute the query two times. And I don't know ON what field to JOIN...

Is it possible without creating a table? (I do not have write permissions)

Solution

Assuming you want Weighed Avg Margin ...

Select
       ...
       sum(amount*margin)/sum(amount) as 'Weighted Avg'
       ...
 From  ...
 Group By ...

Edit - To avoid the dreaded Divide-By-Zero

case when sum(amount)=0 then null else sum(amount*margin)/sum(amount) end as 'Weighted Avg'

Edit 2 - NullIf()

...
sum(amount*margin)/NullIf(sum(amount),0) as 'Weighted Avg'
...

Comments

[SqlZim]

Nice one! I was stuck trying to figure out which one he wanted to use as the weight.

[John Cappelletti]

@SqlZim Jury is still out, OP may be looking for the total WAM.

[John Cappelletti]

@Harper I suspect you were just over-thinking it. Happens to us all :)

[Harper]

Oh, nice, thanks for the update. DIV/0 is a real danger in this.

[John Cappelletti]

@Harper Happy it helped