I am trying to query a transactional historical data having fields - origin, destination, Supplier, rates. Each field have multiple combination with other fields. I want to see the result by origin, by destination, by Supplier, Avg of rates.
I can use the group by function for the above query. But on seeing the rates I understood it has lot of wrong data in it. To correct it, I want that Avg should be taken for the 3 rates (with highest frequency) only and discards all zeros if any.
Further I want only top 5 records should be displayed for each group of Origin, Destination, Supplier.
Please help me in designing this query.
I can use the group by function for the above query. But on seeing the rates I understood it has lot of wrong data in it. To correct it, I want that Avg should be taken for the 3 rates (with highest frequency) only and discards all zeros if any.
Further I want only top 5 records should be displayed for each group of Origin, Destination, Supplier.
Please help me in designing this query.
