How to sort data ASC by total count of word in MYSQL

Try this:

#1st Query
SELECT *,LENGTH(fname)-LENGTH(replace(fname,' ','')) as word_count FROM mytable
ORDER BY word_count ASC;

#2nd Query
SELECT A.fname, LENGTH(A.fname)-LENGTH(replace(A.fname,' ','')) as word_count FROM mytable A LEFT JOIN
(SELECT *,LENGTH(fname)-LENGTH(replace(fname,' ','')) as w_count FROM mytable WHERE fname='Banana Cakes') B
ON A.fname=B.fname
ORDER BY CASE WHEN B.fname IS NOT NULL THEN B.w_count END DESC, 
         CASE WHEN B.fname IS NOT NULL THEN LENGTH(B.fname) END DESC, 
         word_count ASC;

Fiddle here: https://www.db-fiddle.com/f/3gwZj9yMp43dhmzaETKghd/4

So the first one is simple, just need to order by length ascending and fname descending (since your example show Banana Split to return first instead of Banana Cakes despite both have similar length and word count. Alphabetically, 'C' comes first so Banana Cakes should return first).

The second query I made the condition of 'Banana Cakes' query to become a sub-query then LEFT JOIN it with the main table. On the ORDER BY I'm using CASE expression whereby if the result from the LEFT JOIN (result from the sub-query) is not NULL, ORDER using that value first and then the second order condition is similar to the first order condition in the 1st query.

Edit: Adding word count condition for the ordering. Note this word count function is calculating how many space(s) instead of how many word(s). Example: 0 means only a word without any space and 1 means two words with 1 space.