每组的最小值和最大值

Question

I have two table

location:

• location_id
• address

itransfile:

• id
• transactionNumber
• location_id
• itemName
• quantity

I want to get maximum and minimum sold items by locations.

HighestItemName     HighQauntity    LowestItemName      LowQuantity     LocationName

Chicken Burger      50              Tako                5               Gulshan
Chicken Burger      100             Tikka               10              Nipa
Pasta               150             Cheese Burger       12              Liyari
Pizza               200             Chicken Burger      3               F.B.Area

The query I've done so far:

SELECT t.itemName as HighestItemName, sum(t.quantity) as HighQuantity, l.address LocationName
    from itransfile as t join locations as l
    on t.location_id = l.location_id 
    where t.location_id IN(1,2,3,4)
    group by t.location_id

I don't know how will get max and min items from every group.

Sample Data:

ID  TransNumber ItemName        Quantity location_id
1   1234        Chicken Burger  3           1
2   1234        Cheese Burger   1           1
3   1235        Sandwich        4           2
4   1332        Salad           1           4
5   14537       Tikka           1           3   
6   1236        Roll            3           2
7   1333        Biryani         2           4


location_id     address
1               Gulshan
2               Nipa
3               Liyari
4               F.B.Area

Answer 1

This is what you may be looking for if you need it in one query (SQLFiddle):

select
  l.address,
  imax.itemName max_item, max_min.max_q,
  imin.itemName min_item, max_min.min_q
FROM
  (select
    i.location_id, MAX(i.quantity) max_q, MIN(i.quantity) min_q
  FROM
    itransfile i
  GROUP BY
    i.location_id) as max_min
  LEFT JOIN itransfile imax ON (max_min.max_q = imax.quantity)
  LEFT JOIN itransfile imin ON (max_min.min_q = imin.quantity)
  LEFT JOIN location l ON (max_min.location_id = l.location_id)
GROUP BY
  l.location_id

It looks for min/max values and then looks up the item name and location address. The GROUP_CONCAT makes sure that when there are more items with the same min/max quantity, you get all of them.

Alternatively you can get rid of the GROUP BY and GROUP_CONCAT and get all the items in rows if you need to further process them.