Min and Max from each group
I have two tables
Location:
- LOCATION_ID
- address
itransfile:
- ID
- TransactionNumber
- LOCATION_ID
- ITEMNAME
- quantity
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 max
and min
items from each 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
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. GROUP_CONCAT
ensures that when there are more items with the same min / max quantity, you get all of them.
Alternatively, you can get rid of GROUP BY
and GROUP_CONCAT
and get all elements in strings if you need to process them further.
source to share