The purpose of the "1" in the left(middle, 1) is to say that you want to look at the first 1 character on the left of the variable "middle". So say instead you wanted to look at the left 2 characters of a variable containing the word "hello", you would do a left(variable, 2), which would return "he".
As far as working out what the problem with the other query is, I would have to see the full SQL of it, but my guess is that you have put the Count() part in a WHERE clause. If you want to use count in some sort of condition you need to use the HAVING clause. For example, if you were trying to find everybody that shares a surname with somebody else in the table(ie Count(surname>1)), and has a middle name of Peter:
SELECT firstname
FROM nametable
WHERE middlename='Peter'
GROUP BY surname
HAVING Count(surname)>1;
If you don't really understand this example, just have a read up on the GROUP BY and HAVING in any SQL reference.
Hope this helps.
Rory