DOes it work? Do I just need to create new machines with the same template or would it not work because they all need one SQL backend?
You certainly can, however, you indeed need to ensure all web servers access the same SQL backend (high availability solutions like e.g. read replicas aside right now).
Presumably there has only been a simple single instance Wordpress template available when you asked the question, e.g. Single EC2 Instance web server with Amazon RDS database instance? This one would indeed not allow to cluster Wordpress by running it multiple times, rather you'd start separate Wordpress instances each time.
However, the list of AWS CloudFormation Sample Templates has grown and meanwhile there is a Wordpress template for a Highly Available Web Server with Multi-AZ Amazon RDS Instance available as well, which is currently configured to allow 1-5 web server instances talking to a Multi-AZ MySQL Amazon RDS database instance and running behind an Elastic Load Balancer, which orchestrates the web server instances via Auto Scaling.