OK I've analyzed this further with SNES9x debug.
Tales of phantasia uses this (at least at the place I've checked it) :
Main screen (always on): BG0,BG1,BG2,OBJ,
Sub-screen (always on): BG2,
Subscreen addition(half):BG0,BG1,OBJ,
In other words - when a BG0, BG1, or OBJ palette 4-7 pixel is present in the main screen, it is averaged with the subscreen (BG2) (if applicable) then output to the screen. When only a BG2 pixel or OBJ with palette 0-3 is present (which is the case inside the water) the BG2 is send right through screen.
You could get the same configuration with the following setup (theoretical) :
Main screen (always on): BG0,BG1,BG2,OBJ,
Sub-screen (always on): BG0,BG1,OBJ,
Subscreen addition(half):BG2
This means - when a non-transparent BG2 pixel is present in the main screen, it is halved with BG0/BG1/OBJ (all palettes) and output to screen. If no BG0/BG1/OBJ pixel is present, the BG2 will be send right through the screen without being averaged (middle of the water) and if no BG2 pixel is present (a normal place) it is also sent to the screen without being averaged.
The problem is that *all* sprites will now be transparent with the water. What remains is that you could set the pirority bit in the BG2 so that it is above the spritres (= transparent) at the north of the water pool, and it is below the sprites (= non transparent) at the south of the water pool (so that the head of the characters won't look like it's in the water). This could cause problems with fishes suddently appearing non-transparent if they go too close of a south border of the pool, though.