Home Get ONE value from three different tables gives wrong result - postgresql
Reply: 0

Get ONE value from three different tables gives wrong result - postgresql

user5779
1#
user5779 Published in September 21, 2018, 2:19 am

The goal is to get ONE value of remaining time on a task. I know that I cannot sum two joined tables, but this simple query get me confused:

CREATE TABLE tsk
    (tskid int4, tskhr numeric(8,2));    
INSERT INTO tsk
    (tskid, tskhr)
VALUES
    (1,80.5),
    (2,120.7);

CREATE TABLE hr
    (hrid int4, hrtsk int4, hrqty numeric(8,2));    
INSERT INTO hr
    (hrid,hrtsk, hrqty)
VALUES
    (1,1,40.5),
    (2,2,40.7),
    (3,1,1);

CREATE TABLE inte
    (inteid int4, intetsk int4, inteqty numeric(8,2));    
INSERT INTO inte
    (inteid,intetsk, inteqty)
VALUES
    (1,1,10.5);

The desired output is

+-------+------+
| tskid |  hr  | 
+-------+------+
|    1  | 28,5 |  (80,5-(40,5+1+10,5)
|    2  |   80 |  (120,7-40,7)
+-------+------+

My first attempt is simple

SELECT    tskid, coalesce(tskhr-hrqty+inteqty,0) 
FROM      tsk
LEFT JOIN hr on hrtsk=tskid
LEFT JOIN inte on intetsk=tskid

The output

+-------+----------+
| tskid | coalesce |
+-------+----------+
|     1 |     50.5 |
|     1 |       90 |
|     2 |        0 |
+-------+----------+

The second attempt

SELECT    tskid, coalesce(tskhr-(hrqty+inteqty),0)
FROM      tsk
LEFT JOIN (SELECT hrtsk, sum(hrqty)hrqty FROM hr GROUP BY 1) h ON tskid =h.hrtsk
LEFT JOIN (SELECT intetsk, sum(inteqty)inteqty FROM inte GROUP BY 1) i ON tskid =i.intetsk;

gives this result:

+-------+----------+
| tskid | coalesce |
+-------+----------+
|     1 |     28.5 |
|     2 |        0 |
+-------+----------+

The third attempt to sum outside was not correct either:

WITH list AS(
SELECT    tskid, tskhr, hrqty, inteqty 
FROM      tsk
LEFT JOIN hr on hrtsk=tskid
LEFT JOIN inte on intetsk=tskid)
SELECT    tskid, coalesce(sum(tskhr-hrqty+inteqty),0) 
FROM      list
GROUP BY  1

the result:

+-------+----------+
| tskid | coalesce |
+-------+----------+
|     1 |    140.5 |
|     2 |        0 |
+-------+----------+

I must miss something obvious, but I cannot figure out what.

Fiddle: http://sqlfiddle.com/#!15/f11f75/28

No luck with subqueries either.

TIA,

share|improve this question

2 Answers 2

active oldest votes
up vote 2 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305022 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO