SQL Order by with group by clause

Question

I have one stock table which contains the foreign key of product_id, company_id, size_id and price. I need to fetch total companies, product name (ascending order), price and volume (for least price only) - for every product.

I tried,

SELECT pr.product_name, 
       COUNT(DISTINCT stock.company_id) AS total_companies, 
       sz.size_name, 
       stock.price 
FROM stock 
JOIN product pr ON pr.id = stock.product_id 
JOIN size sz ON sz.id = stock.size_id 
GROUP BY stock.product_id 
ORDER BY pr.product_name ASC;

So far, here the size and price are random for every product. If I put MIN(stock.price) in select query then price is correct but then associated size is wrong. If I put condition to select only products with min price, then company count is reduced to 1.

Any help or pointers are much appreciated.

update your question add a proper data sample and the expected result — ScaisEdge, Aug 25 '18 at 11:27
`So far, here the size and price are random for every product.` [Group by clause in mySQL and postgreSQL, why the error in postgreSQL?](https://stackoverflow.com/a/33629201/5070879) — Lukasz Szozda, Aug 25 '18 at 11:27
See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Aug 25 '18 at 11:30
Do you want to get only one product *HAVING* minimum price ? — Madhur Bhaiya, Aug 25 '18 at 11:31
@LukaszSzozda that's why I've mentioned "If I put MIN(stock.price) in select query then price is correct but then associated size is wrong".. tried using aggregate function but its still not solving the problem completely. — Gaurav Singh, Aug 25 '18 at 11:31
@MadhurBhaiya yes, but I also need the associated size with min price. so far by using MIN function I'm getting the price, but size again is random. — Gaurav Singh, Aug 25 '18 at 11:32

score 0 · Answer 1 · answered Aug 25 '18 at 11:34

This is a little tricky in MySQL prior to V8. One method is to use the group_concat()/substring_index() trick:

SELECT pr.product_name, 
       COUNT(DISTINCT s.company_id) AS total_companies, 
       SUBSTRING_INDEX(GROUP_CONCAT(sz.size_name ORDER BY s.price), ',', 1) as size_at_min_price
       MIN(s.price) as min_price
FROM stock s JOIN
     product pr
     ON pr.id = s.product_id JOIN
     size sz
     ON sz.id = s.size_id 
GROUP BY s.product_id 
ORDER BY pr.product_name ASC;

Notes:

This assumes that the sizes do not contain a comma.
If you do have commas in the sizes, this is easily handled using another separator.
GROUP_CONCAT() by default is limited to 1024 bytes. If this is not big enough, then you can make the buffer larger.

score 0 · Answer 2 · answered Aug 25 '18 at 11:36

Based on the OP's comments, using Nested queries:

SELECT inner_nest.* 
FROM (
      SELECT pr.product_name, 
             COUNT(DISTINCT stock.company_id) AS total_companies, 
             sz.size_name, 
             stock.price 
      FROM stock 
      JOIN product pr ON pr.id = stock.product_id 
      JOIN size sz ON sz.id = stock.size_id 
      GROUP BY stock.product_id 
      ORDER BY stock.price ASC 
     ) AS inner_nest
LIMIT 0, 1

SQL Order by with group by clause

2 Answers2