As you affirmed in the comments, the query you currently are trying to work with is very unmaintainable, and is the main issue you're facing while trying to accomplish your goal. Your first priority should be to clean up, refactor, and standardize how the query is written so that you can achieve your goal and future goals when using this query.
In the ...
This is a good example of why you should upgrade your servers regularly - this would be relatively trivial using window functions (see below) - however, it is also possible using version 5.6 of MySQL as follows (all the code below can also be found in this fiddle):
I did this using "pure" SQL - for solutions using MySQL variables, see here (quite ...
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
select YEAR(Sales.saleDate) SalesDateYear
, count(Vehicles.model) 'Sold'
table is a reserved word, so it is not considered a an identifier, but part of the syntaxis. If you have a table table, and a column named table (confusing), you must refer to them with inverted quotes like this: `table`.
Try to avoid reserved words on table and column identifiers, e.g. my_table or table_name.
You can't SELECT like this, where you dynamically SELECT the table name. That's why it's not working. The subselect works, but.. try to replace subselect by a constant (i.e. known) name and it still won't work.
For something like this you either need an if then else flow or dynamic SQL or do it on application side.