Group by is not SUMMING up the resultant data

Question

Post reply

Group by is not SUMMING up the resultant data

upendramkumar

SSC Veteran

Points: 271
More actions
April 21, 2008 at 4:45 pm

#184646

When I run the following query, I expect the sum to add up for the column, Sdkretrocomm & it is not... and here is the output..

Please help !!!

Query

select isnull(ea.id,0) as SDKConsultant,

sum(ea.earn) as SDKRetroComm,

coalesce(B.membernumber,0) as ProwessMember,

coalesce(B.RetroBonus,0) as ProwessRetroComm,

Diff = coalesce((ea.earn - b.RetroBonus),0),

Percentage =

case

when b.RetroBonus = 0 then 1

when ea.earn = 0 then 1

when b.RetroBonus is null then 1

when ea.earn is null then 1

when ea.earn > b.RetroBonus then (1-b.RetroBonus/ea.earn)

when b.RetroBonus > ea.earn then (1-ea.earn/b.RetroBonus)

else 0

end

from [hqvm03\hqsqlinst05].sdk.dbo.DecBonusPaidRBTL_TMT Ea

full outer join

(select A.Membernumber, A.RetroBonus from

(select

bb.beenumber as MemberNumber,

pc.initialadjustmentamount as RetroBonus

from beebusiness bb

join Proadjustment pc on pc.beebusinessguid = bb.beebusinessguid

join beeentity be on be.beeentityguid = bb.beeentityguid

where pc.cmmadjustmenttypeguid in (3218165285136582397821897346, 20290615709734463701490426574)

)A)B

on B.Membernumber = Ea.id and ea.level in ('SCON', 'STMG')

group by isnull(ea.id,0), coalesce(B.membernumber,0),

coalesce(B.RetroBonus,0),

coalesce((ea.earn - b.RetroBonus),0),

(case

when b.RetroBonus = 0 then 1

when ea.earn = 0 then 1

when b.RetroBonus is null then 1

when ea.earn is null then 1

when ea.earn > b.RetroBonus then (1-b.RetroBonus/ea.earn)

when b.RetroBonus > ea.earn then (1-ea.earn/b.RetroBonus)

else 0

end)

______

Output

_____

0048340 26.6000 48340 172.8900

0048340 67.4100 48340 172.8900

0048340 78.8800 48340 172.8900

0050068 9.8400 50068 158.7300

0050068 30.4800 50068 158.7300

0050068 34.0400 50068 158.7300

0050068 84.3600 50068 158.7300

0054504 33.9600 54504 115.1000