I have been wondering do water candles work if you place them on the floor or on the table, or if they only work if it is in your hand.
According to Terraria's official wiki page on Water Candles:
Holding a Water Candle in your hand will increase the monster spawn probability to 133% and the maximum number of monsters to 150%.
They produce light like a regular torch or candle would. If you have them on tables as props, they produce some lovely spooky lighting. However, they don't increase the spawn rate like this.
To increase the spawn rate with a water candle, hold it in your hand. It can't just be in the top row of your inventory - it must be the currently active and held item.
Keep a weapon handy so that you can swap back and forth between your water candle and weapon as necessary. You can't hold both at the same time, but you can at least run around luring enemies out with your water candle, attack them all, then return to luring enemies out.
Water candles placed in the world and those actively held in your hand increase the spawn rate and maximum number of enemies. As of 18.104.22.168 these effects do not stack, and a maximum of one candle will count at a time.
For versions before 1.3 see the old answer below.
According to the disassembled code (22.214.171.124.1), placed water candles also affect spawnrate and max spawns. This effect is separate from holding the candle in your hand and will stack with it. A single placed water candle is enough and only one matters.
NPC.spawnRate = (int)((double)NPC.spawnRate * 0.75); // Spawns happen 33% more often, spawn interval multiplied by 0.75
NPC.maxSpawns = (int)((float)NPC.maxSpawns * 1.5f); // Spawn limit increased by 50%, multiplied by 1.5
Both effects are the same and when stacked (holding a candle and placed a candle) spawnrate is increased by 78% and maximum spawned enemies is increased by 125%.