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:
Product
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
Product Overview
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
The output:
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.
Desired output
Sum of all groups
100
Can anyone lead me to the right direction please?