Wrong sum fetching from several tables - postgresql -


postgresql version 9.1.13

i have 1 table code linked several tables want sum.

so far result not correct values (jsfiddle)

http://sqlfiddle.com/#!15/efedc/18

select code_name,  sum(estrec_sum) est,  sum(hr_sum) total, sum(case when hr_status='0' hr_sum else 0 end) unbill, sum(case when hr_status='1' hr_sum else 0 end) bill code right join hr on code_id=hr_code right join estrec on code_id=estrec_code hr_job='1' , estrec_job='1' group code_name 

the sum of estrec_sum sum estrec_job , hr_status ignored

further testing: running each table separate gets correct values:

select code_name,  sum(case when estrec_job = '1' estrec_sum else 0 end) est code right join estrec on code_id=estrec_code estrec_job = '1' group code_name order code_name 

copy gets 100 on job 1 expected.

select code_name,  sum(case when hr_job = '1' hr_sum else 0 end) total, sum(case when hr_job = '1' , hr_status='0' hr_sum else 0 end) unbill, sum(case when hr_job = '1' , hr_status='1' hr_sum else 0 end) bill code right join hr on code_id=hr_code hr_job = '1' group code_name 

the result correct. fetching 2 tables gets wrong values back.

it seems adding right join destroys result

thanks in advance clues!

sql fiddle

select     code_name,     est,     unbill + bill total,     unbill,     bill     code     right outer join (         select             hr_code code_id,             sum(case when hr_status = '0' hr_sum else 0 end) unbill,             sum(case when hr_status = '1' hr_sum else 0 end) bill         hr         hr_job = '1'         group hr_code ) hr using (code_id)     right outer join (         select             estrec_code code_id,             sum(estrec_sum) est         estrec         estrec_job = '1'         group estrec_code     ) estrec using (code_id) ; 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -