CODEWITHSUNDEEP
postgresql
sibert
sibert

Reputation: 2228

Wrong sum fetching from several tables - postgresql

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)

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

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!

Upvotes: 0

Views: 189

Answers (4)

sibert
sibert

Reputation: 2228

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"?

Upvotes: 0

sibert
sibert

Reputation: 2228

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

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

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)
;

Upvotes: 2

sibert
sibert

Reputation: 2228

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?

Upvotes: 0

Related Questions