2016-07-20 13:22
浏览 118


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

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,
  ((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)
  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

Can anyone lead me to the right direction please?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doushan1863 2016-07-20 13:54

    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)
        SELECT t.group_id, t.new_price
            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
    打赏 评论
  • dqf60304 2016-07-20 13:35

    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
    打赏 评论

相关推荐 更多相似问题