Username79123 Username79123 - 7 months ago 10
SQL Question

Count how many times a value appears in tables SQL

Here's the situation:

So, in my database, a person is "responsible" for job X and "linked" to job Y. What I want is a query that returns: name of person, his ID and he number of jobs it's linked/responsible. So far I got this:

select id_job, count(id_job) number_jobs
from
(
select responsible.id
from responsible
union all
select linked.id
from linked
GROUP BY id
) id_job
GROUP BY id_job


And it returns a table with id in the first column and number of occurrences in the second. Now, what I can't do is associate the name of person to the table. When i put that in the "select" from beginning it gives me all the possible combinations... How can I solve this? Thanks in advance!

Example data and desirable output:

| Person |

id | name
1 | John
2 | Francis
3 | Chuck
4 | Anthony

| Responsible |

process_no | id
100 | 2
200 | 2
300 | 1
400 | 4

| Linked |

process_no | id
101 | 4
201 | 1
301 | 1
401 | 2

OUTPUT:

| OUTPUT |

id | name | number_jobs
1 | John | 3
2 | Francis | 3
3 | Chuck | 0
4 | Anthony | 2

Answer

Try this way

select prs.id, prs.name, count(*) from Person prs
join(select process_no, id
     from  Responsible res 
     Union all
     select process_no, id
     from  Linked  lin ) a on a.id=prs.id
group by prs.id, prs.name