Min and Max from each group
I have two tables
I want to get the maximum and minimum items sold by location.
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 request I've made 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 to get
items from each group.
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
source to share
This is what you might 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 for the address and address of the position.
ensures that when there are more items with the same min / max quantity, you get all of them.
Alternatively, you can get rid of
and get all elements in strings if you need to process them further.
source to share