CODEWITHSUNDEEP
sqlms-access
AnalLiquid
AnalLiquid

Reputation: 13

Finding the Weighted Average in a Query

The question is:For the previous Track, compute the weighted average (instead of the average) average yield by nation (yield is defined as dividend divided by price, and is reported as a percentage). The weighted average includes the quantity of shares as a weight. But I have no clue on how to find a weighted average.

I already have some code that was used to compute the average of the yield in percent. But I'm having trouble computing the weighted average. I talked to some classmates and they said they're trying to use the formula: sum(x*w)/sum(w) where w is weight. But I'm having trouble implementing this into my code

SELECT Nations.nationName, 
AVG(dividend/price) AS Yield
FROM Shares, Nations
WHERE Shares.nationID=Nations.ID
GROUP BY Nations.nationName;

In the end the weighted average should be computer but I just don't know how to implement that into the code. The material we're supposed to base this problem off of is no help.

Upvotes: 0

Views: 505

Answers (1)

GMB
GMB

Reputation: 222702

I followed you correctly, you should consider:

SELECT 
    n.nationName, 
    SUM(s.quantity * s.dividend / s.price)/IIF(SUM(s.quantity) = 0, NULL, SUM(s.quantity)) AS weighted_yield
FROM Shares s
INNER JOIN Nations n ON s.nationID = n.ID
GROUP BY n.nationName;

Notes:

  • always prefer explicit JOINs over old-school implicit JOINs
  • also, using table aliases are a best practice, since they make the query shorter and easier to follow

Upvotes: 1

Related Questions