query with subquery with 1 result(max) for each year

Question

I have to make a query where I show for each year wich shipper had the maximum total cost. My query now show for each year the total cost of each shipper. So in the result i must have a list of the years, for each year the shipper and the total cost. Thanks in advance.

select year(OrderDate), s.ShipperID, sum(freight) 
from orders o
join shippers s on o.ShipVia = s.ShipperID
group by year(OrderDate),s.ShipperID

Answer 1

Select a.FreightYear, a,ShipperID, a.FreightValue
from
(
    select year(OrderDate) FreightYear, s.ShipperID, sum(freight) FreightValue
    from orders o
    join shippers s on o.ShipVia = s.ShipperID
    group by year(OrderDate),s.ShipperID
) a
inner join
(
    select FreightYear, max(FrieghtTotal) MaxFreight
    from
    (
        select year(OrderDate) FreightYear, s.ShipperID, sum(freight) FreightTotal
        from orders o
        join shippers s on o.ShipVia = s.ShipperID
        group by year(OrderDate),s.ShipperID
    ) x
    group by FreightYear
) max on max.FreightYear = a.FreightYear and max.MaxFreight = a.FreightValue
order by FreightYear

Inner query a is your original query, getting the value of freight by shipper.

Inner query max gets the max value for each year, and then query max is joined to query a, restricting the rows in a to be those with a value for a year = to the max value for the year.

Cheers -

Answer 2

It's marginally shorter if you use windowing functions.

select shippers_ranked.OrderYear as OrderYear,
       shippers_ranked.ShipperId as ShipperId,
       shippers_ranked.TotalFreight as TotalFreight
from
(
    select shippers_freight.*, row_number() over (partition by shippers_freight.OrderYear order by shippers_freight.TotalFreight desc) as Ranking
    from
    (
        select year(OrderDate) as OrderYear, 
               s.ShipperID as ShipperId, 
               sum(freight) as TotalFreight
        from orders o
        inner join shippers s on o.ShipVia = s.ShipperID
        group by year(OrderDate), s.ShipperID
    ) shippers_freight
) shippers_ranked
where shippers_ranked.Ranking = 1
order by shippers_ranked.OrderYear
;

You need to decide what you would like to happen if two shippers have the same TotalFreight for a year - as the code above stands you will get one row (non-deterministically). If you would like one row, I would add ShipperId to the order by in the over() clause so that you always get the same row. If in the same TotalFreight case you would like multiple rows returned, use dense_rank() rather than row_number().