Translating Excel's SumIfs and Variance Functions to MySQL Query

Other Questions MySQL
1

Hey everyone, I’m stuck trying to move some Excel calculations to MySQL. I’ve got this data about parts, customers, and orders. In Excel, I use SumIfs to get the ReplenQty and a combo of If and Var for RpInVar. But my MySQL attempt isn’t working right.

Here’s what I tried in MySQL:

SELECT DISTINCT 
item_code, 
client, 
order_date,
quantity,
begin_date,
restock_date,
SUM(CASE WHEN item_code = item_code AND client = client AND order_date BETWEEN begin_date AND restock_date THEN quantity ELSE 0 END) AS restock_quantity,
VARIANCE(CASE WHEN item_code = item_code AND client = client AND order_date BETWEEN begin_date AND restock_date THEN quantity ELSE 0 END) AS restock_variance
FROM 
inventory_data
GROUP BY 
item_code, 
client, 
order_date, 
quantity, 
begin_date, 
restock_date;

The problem is the restock_quantity matches quantity exactly, and restock_variance is always 0. Any ideas what I’m doing wrong? Thanks!

2

hey zack, your query looks a bit off. the CASE statements are redundant cuz you’re already grouping by those columns. try removing the conditions in CASE and just use SUM(quantity) and VARIANCE(quantity). also, you might wanna use a subquery or window function for better results. good luck!