Postgres: How to join multiple tables?

Question

I have 2 tables in postgres10, T1 and T2 like below, I want to have 3 differents queries:

the total of nb_hour_doc + nb_hour_interv for each user in one query;
the total of nb_doc + nb_interv for each user in one query;
the total of nb_hour_doc + nb_hour_interv for each project;

Table T1

| user_id   |      user_name      |  project |  nb_hour_doc |  nb_doc |  doc_start        | doc_end           |
|-----------|:-------------------:|---------:|--------------|:-------:|------------------:|-------------------|

|1001       |Toto                 |OTHERS    |11000         |2        |2018-03-25 12:13:00|2018-03-25 12:15:00|

|1002       |Diana                |GIT       |7000          |1        |2018-03-25 13:00:00|2018-03-25 13:15:00|

|1003       |Alex                 |DEV       |7001          |3        |2018-03-25 11:03:05|2018-03-25 11:35:40|

|1003       |Alex                 |PLUG      |10000         |1        |2018-03-25 12:23:00|2018-03-25 12:25:00|

|1002       |Diana                |PLUG      |7100          |1        |2018-03-25 12:16:00|2018-03-25 12:51:00|

Table T2:

| user_id | user_name | project | nb_hour_interv | nb_interv | int_start | int_end |
|---------|:---------:|--------:|-------------|--------|---------------------|---------------------|
| 1003 | Alex | OTHERS | 71 | 2 | 2018-03-25 12:13:00 | 2018-03-25 12:15:00 |
| 1003 | Alex | DEV | 71 | 1 | 2018-03-25 08:11:00 | 2018-03-25 08:30:00 |
| 1003 | Alex | OTHERS | 171 | 5 | 2018-03-25 02:08:00 | 2018-03-25 02:45:00 |
| 1001 | Toto | NEXT | 31 | 4 | 2018-03-25 13:13:00 | 2018-03-25 13:15:00 |
| 1003 | Alex | PLUG | 10 | 1 | 2018-03-25 14:19:00 | 2018-03-25 14:55:00 |
| 1002 | Diana | FIRST | 70 | 1 | 2018-03-25 17:43:00 | 2018-03-25 17:55:00 |

and a tables containing the information about user:

users:

| user_id | user_name |
|---------|:---------:|
| 1002 | Diana |
| 1003 | Alex |
| 1004 | Boto |
| 1001 | Toto |

I use this query but it didn't give the expected result:

select u.user_id, u.user_name, COALESCE(sum(vd.nb_hour_doc),0) as nb_hour_doc, COALESCE(sum(vd.nb_doc),0) as nb_doc, COALESCE(vi.project,'none') as projet_nom_doc, COALESCE(vd.project,'none') as projet_nom_int, 
COALESCE(sum(vi.nb_hour_interv),0) as nb_hour_interv, COALESCE(sum(vi.nb_interv),0) as nb_interv from users u
LEFT JOIN T1 vd ON vd.user_id = u.user_id
LEFT JOIN T2 vi ON vi.user_id = u.user_id

GROUP BY vi.project, u.user_id,  u.user_name, vd.project

Anyone can help me?

I'd like to have something like this:

the total of nb_hour_doc + nb_hour_interv

| user_id   | user_name     | nb_hour_doc+ nb_hour_interv   |
|---------  |:---------:    |-----------------------------  |
| 1002  | Diana     | 7070  |
| 1003  | Alex  | 17324     |
| 1004  | Boto  | 0     |
| 1001  | Toto  | 11031     |

the total of nb_doc + nb_interv for each user:

| user_id   | user_name     | (nb_doc + nb_interv)  |

|---------  |:---------:    |-------------  |

| 1001      |    Toto       | 6         |
| 1002      |   Diana       | 3         |
| 1003      |    Alex       | 13            |
| 1004      |    Boto       | 0         |

the total of nb_hour_doc + nb_hour_interv for each project

| project   | (nb_hour_doc+ nb_hour_interv) |
|---------  |-------------  |
| OTHERS    | 11242         |
| DEV       | 7072          |
| NEXT      | 31            |
| PLUG      | 10010         |
| FIRST     | 70            |
| GIT       | 7000          |

How is the query working when you are using vi.nb_hour_interv, and vi is a T2 alias, but T2 doesn't have a field called nb_hour_interv? — 404
– 404, Commented Mar 26, 2018 at 13:56

Joakim Danielson · Accepted Answer · 2018-03-27 07:54:17Z

1

I think this should work although I only tested it in MariaDb (MySQL)

Edit: Fixed the last project query

SELECT u.user_id, u.user_name, 
   COALESCE((SELECT SUM(nb_hour_doc) FROM T1 WHERE user_id = u.user_id GROUP BY user_id) + 
            (SELECT SUM(nb_hour_interv) FROM T2 WHERE user_id = u.user_id GROUP BY user_id), 0) AS hours
FROM users u

SELECT u.user_id, u.user_name, 
  COALESCE((SELECT SUM(nb_doc) FROM T1 WHERE user_id = u.user_id GROUP BY user_id) + 
           (SELECT SUM(nb_interv) FROM T2 WHERE user_id = u.user_id GROUP BY user_id), 0) AS hours
FROM users u


SELECT z.project, SUM(z.hour)
FROM (SELECT project, nb_hour_doc as hour 
      FROM T1
      UNION ALL
      SELECT project, nb_hour_interv AS hour
      FROM T2
     ) as z
GROUP BY z.project

edited Mar 27, 2018 at 7:54

answered Mar 26, 2018 at 15:02

Joakim Danielson

53k5 gold badges36 silver badges79 bronze badges

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

2 Comments

Vashia Over a year ago

Fine, it works! I just change the second query to get sum of nb_doc and nb_interv I put the query in this link. sqlfiddle.com/#!15/483d1/3. Thank you

Joakim Danielson Over a year ago

Yeah, sorry about that, copy/paste mistake ;) I'll update my answer.

404 · Accepted Answer · 2018-03-26 15:49:36Z

1

I think this should give you what you're looking for. I've only done the first one, with tables created using the bare minimum required info. It groups by user_id and user_name, and SUMs the values from a couple fields for each user.

SELECT u.user_id, u.user_name, COALESCE(SUM(t1.nb_hour_doc), 0) + COALESCE(SUM(t2.nb_hour_interv), 0) AS result
FROM users u
LEFT JOIN t1 USING (user_id)
LEFT JOIN t2 USING (user_id)
GROUP BY u.user_id, u.user_name

You can test it here: http://sqlfiddle.com/#!15/483d1/2/0

edited Mar 26, 2018 at 15:49

answered Mar 26, 2018 at 14:35

404

8,7522 gold badges33 silver badges53 bronze badges

2 Comments

Vashia Over a year ago

Good, it works for query 1, but how to have the information if one user doesn't have record in T2 and T1?, for example: Boto is 0 on T1 and T2.

404 Over a year ago

@VASHIA I've updated the query, added LEFT JOINs and COALESCE to include users with no records in the other tables. Check out the updated link.

Collectives™ on Stack Overflow

Postgres: How to join multiple tables?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related