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

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

sibert
1#
sibert Published in 2018-01-13 09:23:18Z

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,

Tim Biegeleisen
2#
Tim Biegeleisen Reply to 2018-01-13 09:40:43Z

I would use separate subqueries for the hr and inte tables:

SELECT
    t1.tskid,
    t1.tskhr - COALESCE(t2.hrqty, 0) - COALESCE(t3.inteqty, 0) AS hr
FROM tsk t1
LEFT JOIN
(
    SELECT hrtsk, SUM(hrqty) AS hrqty
    FROM hr
    GROUP BY hrtsk
) t2
    ON t1.tskid = t2.hrtsk
LEFT JOIN
(
    SELECT intetsk, SUM(inteqty) AS inteqty
    FROM inte
    GROUP BY intetsk
) t3
    ON t1.tskid = t3.intetsk;

SQLFiddle

The reason why this approach works is that the subqueries avoid the problem of multiplying records which is inherent with joining. In this case, we only want to count the values from the tsk table once, but we need to aggregate by task ID in the other two tables.

Álvaro Delgado Villalba
3#
Álvaro Delgado Villalba Reply to 2018-01-13 16:43:40Z

try with this:

SELECT tskid, tskhr-((select sum(hrqty) ty from hr where hrid = tsk.tskid group by hrid)+ CASE WHEN (SELECT COUNT(*) FROM inte WHERE inteid=tsk.tskid) = 0 THEN 0 ELSE (SELECT intetsk+inteqty FROM inte WHERE inteid=tsk.tskid group by inteid) END) AS hr FROM tsk group by tskid; 
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO