Match any item in SQL "group by"
I have flight records for the route and guarantee that all prices for the selected route are in the same currency.
With the request, group by
I get the total sum
from the route price:
select ITINERARY_ID, sum(PRICE) from FLIGHT group by ITINERARY_ID;
How can I choose the currency (they are all the same for a given one ITINERARY_ID
)?
I end with min
/ max
:
select ITINERARY_ID, sum(PRICE), max(CURRENCY) from FLIGHT group by ITINERARY_ID;
but it's impractical to sort CURRENCY
if I only want to select any item up (they are still the same).
Is min
/ is it max
only a possible solution? Improve query performance?
source to share
The real solution lies in the design of the table. You say that it is guaranteed that all route prices are in the same currency. But you are storing the currency for the flight, which technically allows different currencies to be used within the route.
Save currency using route and request accordingly:
select i.itinerary_id, f.total_price, i.currency
from itinerary i
join
(
select itinerary_id, sum(price) as total_price
from flight
group by itinerary_id
) f on f.itinerary_id = i.itinerary_id;
source to share
You can include currency
in group by
:
select ITINERARY_ID, currency, sum(PRICE)
from FLIGHT
group by ITINERARY_ID, currency;
Or use the aggregation function:
select ITINERARY_ID, MAX(currency) as currency, sum(PRICE)
from FLIGHT
group by ITINERARY_ID;
If the currencies do match, then they give the same results.
source to share