Calculating Weighted Average in SQL Query

Data and Databases
1

Hey folks, I’m stuck on a SQL problem. I’ve got this query:

SELECT product_id, product_name, unit_cost, stock_qty, SUM(stock_qty)
FROM products 
INNER JOIN inventory ON product_id = inventory_id
WHERE (product_type='FG') AND (stock_qty != '0')

But I need to do more with it. I want to:

  1. Get the total value by multiplying unit_cost and stock_qty
  2. Sum up all stock_qty for each product_id
  3. Find the average cost by dividing total value by total quantity

Can anyone point me in the right direction? I’m not sure how to combine these steps in one query. Any help would be awesome! Thanks in advance!

2

I’ve encountered similar challenges with weighted averages in SQL. Here’s an approach that might work for you:

SELECT
product_id,
product_name,
SUM(unit_cost * stock_qty) / SUM(stock_qty) AS avg_cost,
SUM(stock_qty) AS total_qty,
SUM(unit_cost * stock_qty) AS total_value
FROM products
INNER JOIN inventory ON product_id = inventory_id
WHERE product_type = ‘FG’ AND stock_qty != 0
GROUP BY product_id, product_name

This query calculates the weighted average cost, total quantity, and total value for each product. The GROUP BY clause ensures you get results per product. Remember to test and adjust based on your specific database structure and requirements.

3

yo max, have u tried using subqueries? might help u break it down. something like:

SELECT p.product_id, p.product_name,
(SELECT SUM(unit_cost*stock_qty)/SUM(stock_qty) FROM inventory WHERE inventory_id=p.product_id) AS avg_cost
FROM products p
WHERE p.product_type=‘FG’

just an idea, lemme know if that helps!

4

hey there! have you tried using window functions? they’re super handy for this kinda stuff. maybe something like SUM(unit_cost * stock_qty) OVER (PARTITION BY product_id) could work? just brainstorming here. what other approaches have u considered? curious to hear your thoughts!