CODEWITHSUNDEEP
phpmysqlsummin
shieldcy
shieldcy

Reputation: 602

Get sum of min values in each group from Mysql

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?

Upvotes: 2

Views: 1626

Answers (2)

Diogo Medeiros
Diogo Medeiros

Reputation: 328

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

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 2

Related Questions