How to return the highest value for each different line parameter

advertisements

I'm trying to become the MAX value of a set of rows, on every year that is displayed. I can see what I have to do, I just can't seem to translate it into code.

select YEAR(O.Orderdate) [Year] , SUM(O.Freight) [Total Freight], S.CompanyName
from Orders O inner join Shippers S on S.ShipperID = O.ShipVia
group by S.CompanyName, YEAR(O.Orderdate)

1996 4233,78

1996 2297,42

1996 3748,67

1997 11413,35

1997 8681,38

1997 12374,04

1998 4865,38

1998 5206,53

1998 12122,14

The code returns the total freight for each company (although company name not displayed), per year. I then need to get the highest amount of freight, for every year.

I used following code, but it returns the highest value for all years, and although I know it's wrong, I cannot see the logic or a way to return the highest value for every year separately...

select YEAR(O.Orderdate) [Year] , SUM(O.Freight) [Total Freight]
from Orders O inner join Shippers S on S.ShipperID = O.ShipVia
group by S.CompanyName, YEAR(O.Orderdate)
having SUM(O.Freight) >= ALL (
                                select SUM(O.Freight)
                                from Orders O join Shippers S on S.ShipperID = O.ShipVia
                                group by YEAR(O.Orderdate), S.CompanyName
                            )

This returns:

1997 12374,04

While my goal would be:

1996 4233,78

1997 12374,04

1998 12122,14

I don't need a blatant solution, I'd really much rather have the logic in what I'm doing wrong/what I should be doing. Thanks in advance !


Your query is checking for sum of freight values being greater than equal to all values, so only the maximum value from year 1997 is matched , so only one row is returned

if you want maximum per year, i would go with @Gordon answer, but as per comment, you want to do it using Group by and having.

here is another way by having your current query in subquery.

SELECT Year, Max(TotalFreight) as [Total Freight]
FROM (
select YEAR(O.Orderdate) [Year] , SUM(O.Freight) [TotalFreight]
from Orders O inner join Shippers S on S.ShipperID = O.ShipVia
group by S.CompanyName, YEAR(O.Orderdate) T
group by Year