MySQL GROUP BY with MIN() – Troubleshooting Wrong Results

aggregategroup byMySQL

I have a table that looks like this:

+------+--------+----------+------+--------+-------+------+------------+
| id   | code   | category | mq   | weight | weave | show | min(price) |
+------+--------+----------+------+--------+-------+------+------------+
|    1 | DT450R | carbon   |    1 |    450 | plain |    1 |         90 |
|    2 | DT450R | carbon   |    2 |    450 | plain |    1 |         40 |
|    3 | DT450R | carbon   |    5 |    450 | plain |    1 |         75 |
|    7 | PP120Q | carbon   |    3 |    120 | twill |    1 |         28 |
|    8 | PP120Q | carbon   |    7 |    120 | twill |    1 |         65 |
|    9 | PP120Q | carbon   |    9 |    120 | twill |    1 |         49 |
|    4 | ZX300R | carbon   |    1 |    300 | plain |    0 |         12 |
|    5 | ZX300R | carbon   |   15 |    300 | plain |    1 |        128 |
|    6 | ZX300R | carbon   |   30 |    300 | plain |    1 |         92 |
+------+--------+----------+------+--------+-------+------+------------+

I've created a sqlfiddle here.

I want min price from table in each code. I tried using the following query:

select id, code, category, mq, weight, weave, price, `show`, min(price) as total 
from product group by code;

Why is the group by getting the wrong result? It's returning id = 1 instead of id =2.

Incorrect output:

+------+--------+----------+------+--------+-------+------+------------+
| id   | code   | category | mq   | weight | weave | show | min(price) |
+------+--------+----------+------+--------+-------+------+------------+
|    1 | DT450R | carbon   |    1 |    450 | plain |    1 |         40 |
|    7 | PP120Q | carbon   |    3 |    120 | twill |    1 |         28 |
|    4 | ZX300R | carbon   |    1 |    300 | plain |    0 |         12 |
+------+--------+----------+------+--------+-------+------+------------+

Expected output:

+------+--------+----------+------+--------+-------+------+------------+
| id   | code   | category | mq   | weight | weave | show | min(price) |
+------+--------+----------+------+--------+-------+------+------------+
|    2 | DT450R | carbon   |    2 |    450 | plain |    1 |         40 |
|    4 | ZX300R | carbon   |    1 |    300 | plain |    0 |         12 |
|    7 | PP120Q | carbon   |    3 |    120 | twill |    1 |         28 |
+------+--------+----------+------+--------+-------+------+------------+

Best Answer

As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior.

As example, Aaron Bertrand answered the post Why do we use Group by 1 and Group by 1,2,3 in SQL query? where he described MySQL's GROUP BY as cowboy who-knows-what-will-happen grouping. I just had to agree.

SUGGESTION

Rewrite the GROUP BY using code

select code,min(price) as total 
from product group by code

Do three things

Make the query a subquery
Use price as alias instead on total
Join it back to the product table on code and price

Here is the proposed query

select b.* from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price);

select b.* from
(select code,min(price) as price from product group by code) a
inner join product b ON a.code=b.code AND a.price=b.price;

Checkout the SQL Fiddle for this

GIVE IT A TRY !!!

UPDATE 2017-01-06 16:17 EST

If there exists more than 1 row with the same minimum price for a given code, you have take the query, make it a subquery, join it to retrieve the minimum id for each (code,price) and join that back to product by id:

select bb.* from
(select a.code,a.price,min(b.id) id from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price)
group by a.code,a.price) aa
inner join product bb using (id);

Checkout the SQL Fiddle for that

Related Solutions

Mysql – Selecting minimum value using a subquery

Modifying slightly your second query, will give you both the merchant id and the lowest price (over all products that pass the conditions - I guess that's what you want):

SELECT p.p_m_id, MIN(p_price) AS min_p_price 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY p.p_m_id ;

Then you can join this - as a derived table - to all the tables that you need data from in the results:

SELECT
    m.*, p.*, ga.*                     -- whatever columns you want  
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
JOIN
      ( SELECT p.p_m_id, MIN(p_price) AS p_price 
        FROM tgmp_affiliates ga 
        JOIN tgmp_prices p 
            ON ga.a_code = p.p_gtin 
                AND ga.a_code > '' 
        JOIN tgmp_merchants m 
            ON m.m_id = p.p_m_id 
        WHERE ga.site_id = '34' 
            AND p.site_id = '34' 
            AND ga.a_parent = '25573' 
            AND p.p_type = 'games' 
            AND m.m_hide = 0 
        GROUP BY p.p_m_id 
      ) AS tmp
    ON  tmp.p_m_id = p.p_m_id 
    AND tmp.p_price = p.p_price
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
ORDER BY p.p_price ;

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ? In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select 
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE 
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.