Looking for MAX amoung COUNT values

1

Hello! I use Northwind dataset to solve the problem: "Which categories of goods were companies (having fax) interested in at most in 1997? I did not understand how to find MAX among COUNT values.
I need to do it with subquery as well as with Join.
I cannot use TOP 1, because there are more than 1 maximum values. So, I need max. I tried to do the following. However, it shows a mistake in MAX(...). But I did not understand why as ... gives a column if I execute it separately.

SELECT CategoryName, COUNT(CompanyName) AS num FROM Customers cus, Categories cat, Orders o, [Order Details] od, Products p
WHERE cus.CustomerID=o.CustomerID AND o.OrderID=od.OrderID AND p.ProductID=od.ProductID AND p.CategoryID=cat.CategoryID AND YEAR(OrderDate)='1997' AND Fax!='NULL'
GROUP BY CategoryName
HAVING COUNT(CompanyName) =MAX(
SELECT COUNT(CompanyName) FROM Customers cus, Categories cat, Orders o, [Order Details] od, Products p
WHERE cus.CustomerID=o.CustomerID AND o.OrderID=od.OrderID AND p.ProductID=od.ProductID AND p.CategoryID=cat.CategoryID AND YEAR(OrderDate)='1997' AND Fax!='NULL'
GROUP BY CategoryName
)

Thank you in advance!

3

Plural categories, so you may want to show them all just sorted desc by count of the categories purchased.

Also the following is old school, might want to use proper joins

FROM Customers cus, Categories cat, Orders o, [Order Details] od, Products p
WHERE