2

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?

Improve this question
6
  • i think you should limit 1 so you can get only one value? Commented Jul 20, 2016 at 13:24
  • Hello Ahmad, I tried to limit it already in the 'select query' within the 'AND p.price =' in the bottom but it doesn't work. Commented Jul 20, 2016 at 13:26
  • In your query you are using attribute_id in your joins, but don't mention that column in your structure Commented Jul 20, 2016 at 13:38
  • I am sorry Philipp, it meant to be model_id. Going to fix it now Commented Jul 20, 2016 at 13:40
  • Alright, do you want to solve Issue a) AND b) in this one query? Commented Jul 20, 2016 at 13:44

2 Answers 2

Reset to default
2

You can use the following query:

SELECT SUM(min_price)
FROM (
  SELECT po.group_id, 
         MIN(CASE WHEN pgo.group_id IS NULL THEN price ELSE 0 END) AS min_price
  FROM Product AS p
  INNER JOIN Product_overview AS po 
     ON p.product_id = po.product_id AND p.model_id = po.model_id
  LEFT JOIN Product_group_optional AS pgo ON po.group_id = pgo.group_id
  GROUP BY po.group_id) AS t
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hello Giorgos, thanks a lot for the suggestion. Will try it out and let you know if it works
Thanks again for the help Giorgos. It didn't work the way I wanted but some bits of your query helped me a lot to fix the issue.
1

I'm not sure that I understand the keys of your tables, and the problem as well.
There is few questions.
a) The answer should be 120?
b) If the Product has no price, the is price null?
c) If there is a Product in a group with null price and others with price, should it be counted as 0?

Here is how you could get the sum of the lower prices of each group, ignoring the product_group_optional for while:

SELECT t2.group_id, sum(t2.new_price)
FROM
(
    SELECT t.group_id, t.new_price
    FROM
    (
        SELECT po.group_id, if(ifnull(pgo.product_id, true), p.price, 0) as new_price
        FROM product p, product_overview po
            LEFT JOIN product_group_optional pgo ON po.group_id = pgo.group_id
        WHERE p.model_id = po.model_id
        ORDER by po.group_id, new_price
    ) t
    GROUP BY t.group_id
) t2
Improve this answer

5 Comments

Hello Diogo, thanks for a lot for the help a) The answer should be 100 overall. Because as you can see in Group A, there are two models (model id 1 and 3) with the value 100. But I just want to get that minimal value once. In Group B, the minimum value is 20 BUT due to the fact that group B is optional (as you can see there is a record in table product group optional) then price is automatically set to 0. That is why overall value should be 100 and not 120 b) if product has no price, then price should be '0' c) yes it should be counted as 0 :)
As I can see, your query works fine. The most tricky part is to turn values to 0 when group is set to optional. As you said, Group B will return min value 20. But I want to make it 0 because B is optional. If you have a suggestion for this would be great. Thanks again
I figured it out myself, no problem. Thanks for the help.
Great @savvas999. I've edited the SQL. Please, try this one.
Hello, I can't figure out why your suggestion (for optional groups) doesn't work but I will post my solution in the first post just in case anyone wants to know ;)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.