TABLE 1 employee:

employee_id, first_name, last_name
     2           John    Appleseed

TABLE 2 performance_review:

employee_id, reviewer_id
     2            1
     2            3
     2            4
     1            2
     3            2 

QUESTION: print the first_name and last_name in a single row, then how many times that id is found in the employee_id column, then how many times that same id is found in the reviewer_id column.

Example output:

Name               Employee_id count   Received_review count
-------------------------------------------------------------
John Appleseed              3                2

What I got so far (it doesn't work)

SELECT 
    CONCAT([employee_first_name], ' ' , [employee_last_name]) AS employee_full_name, 
    (SELECT COUNT(employee.employee_id) 
     FROM performance_review AS received_review 
     LEFT JOIN performance_review ON employee.employee_id = performance_review.employee_id) AS received_reviews
FROM 
    employee

CodePudding user response：

Since this involves separate aggregation over two different columns you need two subqueries, one for each.

Here is an example [edit] left joins should be used here because the inner joins would fail for example if the performance review table has all rows with null reviewer for a particular employee.

with 
  emp as (select employee_id,count(*) employee_count 
          from performance_review 
          group by employee_id),
  rev as (select reviewer_id,count(*) reviewer_count 
          from performance_review 
          group by reviewer_id) 
select 
  first_name,
  last_name,
  employee_count,
  reviewer_count
from 
 employee 
left join emp on employee.employee_id=emp.employee_id 
left join rev on employee.employee_id=rev.reviewer_id;

The result

CodePudding user response：

Robert's answer is the clearest way to do it but I thought I would show another way to do it with a join -- here you use a trick of doing a test and sum to count certain items. I join both cases

   SELECT e.first_name, e.last_name,
          SUM(CASE WHEN e.employee_id = p.employee_id THEN 1 ELSE 0 END) as employee_count,
          SUM(CASE WHEN e.employee_id = p.reviewer_id THEN 1 ELSE 0 END) as reviewer_count
   FROM employee e
   LEFT JOIN performance_review p on e.employee_id = p.reviewer_id
                                  or e.employee_id = p.employee_id
   GROUP BY e.first_name, e.last_name