Group by is not SUMMING up the resultant data

Question

Group by is not SUMMING up the resultant data

upendramkumar

SSC Veteran

Points: 281
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
_____
004834026.600048340172.8900
004834067.410048340172.8900
004834078.880048340172.8900
00500689.840050068158.7300
005006830.480050068158.7300
005006834.040050068158.7300
005006884.360050068158.7300
005450433.960054504115.1000

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Anam Verma SSCertifiable Points: 5365 More actions · Answer 1

can you please post the exact results you got after running above query as number of columns in sql query dont match with your output results?

steveb. SSC-Forever Points: 46733 More actions · Answer 2

it looks like you are grouping the same field that you are summing, so it will never sum correctly

post your exact output and it will be easier to diagnose.

upendramkumar SSC Veteran Points: 281 More actions · Answer 3

I prettty much pasted everything that I was seeing & that was that. Anyway, I got this sorted out by creating a derived table after grouping them.

Thanks one & All !!!