I am trying to retrieve the minimum price of some models. Each model belongs to a certain group which belongs to a product.
I have the following tables:
model_id product_id price 1 1 100 2 1 120 3 1 100 4 1 200 5 1 250 10 1 20 11 1 50 12 1 50
model_id product_id group_id 1 1 A 2 1 A 3 1 A 4 1 A 5 1 A 10 1 B 11 1 B 12 1 B
Product Group Optional
group_id product_id B 1
Some groups could be optional, which means price will be zero unless the member wants to choose otherwise.
So in the example above, I want to get the sum of minimum price from each group. We have two groups, group A and group B. Group A minimum price value is 100 (model_id 1 and 3) Group B minimum price value is 20 (model_id 10) but because Group B is optional then that means minimum price value is 0.
Overall sum of min values: 100 (Group A) + 0 (Group B) = 100
My code so far:
SELECT po.group_id, CASE WHEN ((SELECT COUNT(*) FROM product_group_optional pgo WHERE po.group_id = group_id AND po.product_id = 1 AND po.product_id = product_id) >= 1) THEN SUM(0) ELSE SUM(p.price) END AS sum_price FROM product_overview po, product p WHERE po.product_id = 1 AND po.model_id = p.model_id AND p.price = ( SELECT MIN(p2.price) FROM product p2, product_overview po2 WHERE po2.product_id = 1 AND po2.group_id = po.group_id AND po2.model_id = p2.model_id ) GROUP BY po.group_id
group_id sum_price A 200 B 0
The problem is that I get 200 for Group A but it should be 100. There are 2 models with min value 100, model 1 and 3. And I assume these are sum together = 100 + 100 = 200.
Issue a) But I want to just take the min value, no matter how many times this value exists.
Issue b) Also, I am trying to get the SUM of those two output SUM of Group A and Group B.
But I am not sure how to do it. I want it to be done in this query.
Sum of all groups 100
Can anyone lead me to the right direction please?