Summarize count of multi table in single SQL query

Question

I have three table with below details:

Table 1: worklog

+-----------+------------+-------------+
| worklogid | technician | description |
+-----------+------------+-------------+
| 1         | john       | some text   |
+-----------+------------+-------------+
| 2         | jack       | some text   |
+-----------+------------+-------------+
| 3         | john       | some text   |
+-----------+------------+-------------+
| 4         | jenifer    | some text   |
+-----------+------------+-------------+

Table 2: task

+--------+-------+-------------+
| taskid | owner | description |
+--------+-------+-------------+
| 1      | john  | some text   |
+--------+-------+-------------+
| 2      | john  | some text   |
+--------+-------+-------------+
| 3      | john  | some text   |
+--------+-------+-------------+
| 4      | jack  | some text   |
+--------+-------+-------------+

Table 3: request

+-----------+------------+-----------+-------------+
| requestid | technician | title     | description |
+-----------+------------+-----------+-------------+
| 1         | john       | some text | ...         |
+-----------+------------+-----------+-------------+
| 2         | sara       | some text | ...         |
+-----------+------------+-----------+-------------+
| 3         | john       | some text | ...         |
+-----------+------------+-----------+-------------+
| 4         | jack       | some text | ...         |
+-----------+------------+-----------+-------------+

Now I need to SQL query for this result:

+------------+------------------+---------------+------------------+
| technician | count(worklogid) | count(taskid) | count(requestid) |
+------------+------------------+---------------+------------------+
| john       | 2                | 3             | 2                |
+------------+------------------+---------------+------------------+
| jack       | 1                | 1             | 1                |
+------------+------------------+---------------+------------------+
| jenifer    | 1                | 0             | 0                |
+------------+------------------+---------------+------------------+
| sara       | 0                | 0             | 1                |
+------------+------------------+---------------+------------------+

What should I do?

I believe, database design has to be improved. No primary key or id is assigned to users. — AT-2017
– AT-2017, Commented Jun 27, 2021 at 11:25

Gordon Linoff · Accepted Answer · 2021-06-27 11:58:57Z

2

One method is to just use union all and aggregation:

select techician, sum(is_workid), sum(is_taskid), sum(is_requestid)
from ((select technician, 1 as is_workid, 0 as is_taskid, 0 as is_requestid
       from worklog
      ) union all
      (select owner, 0, 1, 0
       from task
      ) union all
      (select technician, 0, 0, 1
       from request
      )
     ) t
group by technician;

In Postgres, you can also aggregate before joining:

select *
from (select technician, count(*) as num_workid
      from worklog
      group by technician
     ) w full join
     (select owner as technician, count(*) as num_task
      from task
      group by owner
     ) t
     using (technician) full join
     (select technician, count(*) as num_request
      from request
      group by technician
     ) w 
     using (technician);

With a full join, I find that using is simpler than on clauses. But the name needs to be the same in all the tables.

edited Jun 27, 2021 at 11:58

answered Jun 27, 2021 at 11:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Ehsan Ali Over a year ago

I need to count of each record

Ehsan Ali Over a year ago

Error me: Whenever a sub query is used in from clause, kindly use an alias name for the sub query @GordonLinoff

Gordon Linoff Over a year ago

@EhsanAli . . . This has an alias, t.

Collectives™ on Stack Overflow

Summarize count of multi table in single SQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related