MySQL Group by query over multiple primary key table

Question

I'm having problem with GROUP BY query in a specific table which has two column primary key.

My table looks like like this:

   CREATE TABLE IF NOT EXISTS `stocks` (
      `id_city` int(10) NOT NULL,
      `id_prod` int(10) NOT NULL,
      `sell_price` int(10) DEFAULT '0',
      PRIMARY KEY (`id_city`,`id_prod`)
    )

Let's say it has such values inserted:

INSERT INTO `stocks` (`id_city`, `id_prod`, `sell_price`) 
VALUES ('1', '1', '100'), ('2', '1', '90'), ('3', '1', '10');

After such query:

SELECT id_prod, id_city, MIN(sell_price)
            FROM stocks 
            GROUP BY id_prod

the result row looses 'id_city' key - it takes the first occurance of city_id,

id_prod     id_city     MIN(sell_price)
1           1           10

How to build a proper query for this case? The result should look like this:

id_prod     id_city     MIN(sell_price)
1           3           10

what represent the number 3 ? the last id or the count if id_city ? — echo_Me
– echo_Me, Commented Jan 10, 2013 at 13:49

Taryn · Accepted Answer · 2013-01-10 13:50:59Z

2

If you want to return the record that holds the min(sell_price) you can use a subquery:

SELECT s1.id_prod, 
  s1.id_city, 
  s1.sell_price
FROM stocks s1
inner join
(
  select id_prod,  MIN(sell_price) sell_price
  from stocks
  group by id_prod
) s2
  on s1.id_prod = s2.id_prod
  and s1.sell_price = s2.sell_price

See SQL Fiddle with Demo

This returns:

| ID_PROD | ID_CITY | SELL_PRICE |
----------------------------------
|       1 |       3 |         10 |

answered Jan 10, 2013 at 13:50

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul K Over a year ago

Great! Is it only workaround or just my query cannot be done with 'group by'?

Taryn Over a year ago

Not in your situation. If you GROUP BY only the ID_PROD, then there is no guarantee as to what id_city will be returned. You cannot GROUP BY id_city since you would return all 3 records because they are distinct values.

echo_Me · Accepted Answer · 2013-01-10 14:06:01Z

1

you mean like that using max(id_city)

    SELECT id_prod, max(id_city) city , MIN(sell_price)
        FROM stocks 
        GROUP BY id_prod

sql demo

or like that using count(id_city)

     SELECT id_prod, count(id_city) city, MIN(sell_price)
        FROM stocks 
        GROUP BY id_prod

sql demo

both returns

  ID_PROD   CITY    MIN(SELL_PRICE)
      1        3                10

EDIT

try this by INNER JOIN

   SELECT s.id_prod, 
          s.id_city, 
          s.sell_price
   FROM stocks s
   inner join
             (
              select id_prod,  MIN(sell_price) sell_price
              from stocks
               group by id_prod
              ) s1
     on s.id_prod = s1.id_prod
     and s.sell_price = s1.sell_price

DEMO SQLFIDDLE

edited Jan 10, 2013 at 14:06

answered Jan 10, 2013 at 13:53

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

2 Comments

Paul K Over a year ago

No. I want to return 'id_city' which has lowest 'sell_price' for certain products.

Paul K Over a year ago

Now it works fine. Looks almost the same as @bluefeet ;). Thanks for both answers!

Tikkes · Accepted Answer · 2013-01-10 13:46:59Z

0

you will have to add id_city to the GROUP BY clause as well

http://www.w3schools.com/sql/sql_groupby.asp

answered Jan 10, 2013 at 13:46

Tikkes

4,6874 gold badges38 silver badges66 bronze badges

2 Comments

Paul K Over a year ago

This would return all record separated by id_city. I want to have ONE record with lowest price for product with proper id_city.

Tikkes Over a year ago

in that case, see @bluefeet answer. That is spot on I believe

Collectives™ on Stack Overflow

MySQL Group by query over multiple primary key table

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related