Wrong sum fetching from several tables - postgresql

Question

PostgreSQL version 9.1.13

I have one table code that are linked to several tables that I want to sum.

So far I get a result but not correct values (jsfiddle)

SELECT code_name, 
SUM(estrec_sum) as est, 
SUM(hr_sum) as total,
SUM(case when hr_status='0' then hr_sum ELSE 0 END) as unbill,
SUM(case when hr_status='1' then hr_sum ELSE 0 END) as bill
FROM code
RIGHT JOIN hr ON code_id=hr_code
RIGHT JOIN estrec ON code_id=estrec_code
WHERE hr_job='1' AND estrec_job='1'
GROUP BY code_name

The sum of estrec_sum does sum all estrec_job and the hr_status is ignored

Further testing: Running each table separate gets correct values:

SELECT code_name, 
SUM(case when estrec_job = '1' then estrec_sum else 0 end) as est
FROM code
RIGHT JOIN estrec ON code_id=estrec_code
WHERE estrec_job = '1'
GROUP BY code_name
ORDER BY code_name

Copy gets 100 on job 1 as expected.

SELECT code_name, 
SUM(case when hr_job = '1' then hr_sum else 0 end) as total,
SUM(case when hr_job = '1' and hr_status='0' then hr_sum ELSE 0 END) as unbill,
SUM(case when hr_job = '1' and hr_status='1' then hr_sum ELSE 0 END) as bill
FROM code
RIGHT JOIN hr ON code_id=hr_code
WHERE hr_job = '1'
GROUP BY code_name

the result is also correct. But fetching from two tables gets wrong values back.

It seems that adding another RIGHT JOIN destroys the result

Thanks in advance for any clues!

Please share the table structures for the code,hr,estrec and tell me what wrong is the output. — smn_onrocks
– smn_onrocks, Commented Nov 27, 2014 at 9:28
Thank you for providing the sqlfiddle. Now we know how wrong results look like for this data. An now, please also show us what is an expected (correct) result for this data. — krokodilko
– krokodilko, Commented Nov 27, 2014 at 22:12
look at i.stack.imgur.com/YpIKZ.png above. It seems that all conditions not are working. — sibert
– sibert, Commented Nov 28, 2014 at 6:31

Clodoaldo Neto · Accepted Answer · 2014-11-28 14:11:43Z

2

SQL Fiddle

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

edited Nov 28, 2014 at 14:11

answered Nov 28, 2014 at 13:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sibert · Accepted Answer · 2014-11-28 13:50:34Z

0

I found a bit of a solution:

SELECT code_name, 
(SELECT sum(estrec_sum) from estrec where estrec_code = code_id and estrec_job = '1') as est, 
(SELECT sum(hr_sum) from hr where hr_code=code_id  and hr_job= '1') as total,
(SELECT sum(hr_sum) from hr where hr_status='0' and hr_code = code_id and hr_job= '1') as unbill,
(SELECT sum(hr_sum) from hr where hr_status='1' and hr_code = code_id and hr_job= '1') as bill
FROM code

though this will result in "empty rows". How do I get rid of them?

answered Nov 28, 2014 at 13:50

sibert

2,2688 gold badges39 silver badges62 bronze badges

Comments

sibert · Accepted Answer · 2014-11-29 07:09:40Z

0

Thanks a lot! I am impressed!

This query works perfect as long as there is corresponding codes in the hr table. Adding "WEB" to job 1 breaks the code. And it does not fetch "code_name" for this row.

http://sqlfiddle.com/#!1/8cfe2/1

answered Nov 29, 2014 at 7:09

sibert

2,2688 gold badges39 silver badges62 bronze badges

Comments

sibert · Accepted Answer · 2014-11-29 10:06:16Z

0

The original solution from Clodoaldo Neto "Right outer join" gives an incomplete result.

http://sqlfiddle.com/#!1/fd6f7/1 (Clodoaldo Neto but changed to left outer join)

http://sqlfiddle.com/#!1/fd6f7/2 (my solution)

Any idea how to get rid of the "empty rows"?

answered Nov 29, 2014 at 10:06

sibert

2,2688 gold badges39 silver badges62 bronze badges

Collectives™ on Stack Overflow

Wrong sum fetching from several tables - postgresql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related