SELECT DISTINCT returns only unique rows. Your query, even though it is highly involved, only returns a single row with a single field when you use the SUM() version.
When you do
SELECT DISTINCT pp.EffectivePrice, you're eliminating any rows which happen to have the same EffectivePrice value.
so, given some fake return data of
$1.00 $2.00 $1.00 $3.00
the DISTINCT will hide the duplicate 1 + 3 = 1 from the actual total.Answer from Marc B on Stack Overflow