Select maximum of a count in a grouped clause I have the following tables:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)

When I join these tables using:
select YEAR(Sales.saleDate)
     , Vehicles.model
     , count(Vehicles.model) 'Sold'
     , Vehicles.category
  from Vehicles 
  JOIN vehicleSold
    on Vehicles.vin = vehicleSold.vin
  JOIN Sales 
    on Sales.saleID = vehicleSold.saleID
 group 
    by YEAR(Sales.saleDate)
     , Vehicles.model
     , Vehicles.category;

Result is:
+----------------------+-------------+------+----------------+
| YEAR(Sales.saleDate) | model       | Sold | category       |
+----------------------+-------------+------+----------------+
|                 2020 | Altima      |    1 | car            |
|                 2020 | Flying Spur |    2 | car            |
|                 2020 | Lifan E3    |    3 | Electric Moped |
|                 2020 | Ridgeline   |    2 | truck          |
|                 2020 | Shiver      |    4 | motorbike      |
+----------------------+-------------+------+----------------+

Out of this table I want to get the model that was most sold in a category. So, in this case I only want to return a 2020, Flying Spur, car as the only row in category car because it was the most sold in 2020 in its category. I tried using a subquery is MAX(COUNT(*)) but I guess that is not supported in mysql. If anyone could point out my mistake and has any idea how to do this then that would be big help!
You can use a window function like ROW_NUMBER() to generate a unique ID for each record within a grouping (PARTITION) of each value of category like so:
SELECT SalesDateYear, model, Sold, category
FROM
(
    select YEAR(Sales.saleDate) SalesDateYear
         , Vehicles.model
         , count(Vehicles.model) 'Sold'
         , Vehicles.category
         , ROW_NUMBER() OVER (PARTITION BY Vehicles.category ORDER BY COUNT(Vehicles.model) DESC) SortId
      from Vehicles 
      JOIN vehicleSold
        on Vehicles.vin = vehicleSold.vin
      JOIN Sales 
        on Sales.saleID = vehicleSold.saleID
     group 
        by YEAR(Sales.saleDate)
         , Vehicles.model
         , Vehicles.category
) Results
WHERE SortId = 1

(I normally like to use a CTE instead of subquery for these kinds of things, but I don't think your version of MySQL has CTEs, so the above uses a subquery.)