Adding two extra columns to result set by joining other tables

Question

I have 4 tables:

tasks table

 (task_id , department_id , task_title , task_description , task_start_date , task_due_date , task_rating , task_is_completed)

employees table

 (employee_id , department_id , employee_name , employee_salary ,  employee_hire_date)

departments table

 (department_id , department_name)

employees_tasks join table

 (employee_id , task_id)

Each table is an entity in room database.

I want to return 2 extra columns with the (select * from employees) one is for calculating employee's rating (by getting average of task_rating column in tasks table the tasks must be completed) the other column is to show the number of tasks running for that employee (by getting count of rows in tasks with task_is_completed = 0 )

I don't know which table to join with which table. we managed to make two separate SQL statements that return those 2 columns by using union and left joins but they are pretty ugly and when combining them it doesn't work.

what we have tried

select employees.employee_name , employees.employee_id ,avg(tasks.task_rating)  as Ratings from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id )AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 1 group by (employees.employee_name ) 
union select employees.employee_name, employees.employee_id, avg(0) as Ratings from employees where employees.employee_id  not in (select employees.employee_id from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id ) AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 1 group by (employees.employee_name ) ) group by employees.employee_id order by employees.employee_id ;

select employees.employee_name , employees.employee_id ,count(tasks.task_title)  as tasks_Running from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id )AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 0  group by (employees.employee_name ) 
union select employees.employee_name , employees.employee_id ,0   as tasks_Running  from employees  where (employees.employee_id  not in (select employees.employee_id from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id )AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 0  group by (employees.employee_name )))group by (employees.employee_name) order by employees.employee_id ; 

We want the output to be like this

 (employee_id , department_id , employee_name , employee_salary ,  employee_hire_date , ratings , numTasksRunning)

Answer 1

I believe the following may suit :-

WITH 
  -- Common Table Expression 1 - Average of Completed Tasks per employee
    employee_completedtask_info AS (
        SELECT employees.employee_id,avg(tasks.task_rating) AS atr
            FROM employees_tasks
                JOIN tasks ON employees_tasks.task_id = tasks.task_id
                JOIN employees ON employees_tasks.employee_id = employees.employee_id
            WHERE tasks.task_is_completed > 0
            GROUP BY employees.employee_id
    ),
    -- Common Table Expression 2 - Incompleted Taks per employee
    employee_notcompleted_info AS (
        SELECT employees.employee_id,count() AS itc
            FROM employees_tasks
                JOIN tasks ON employees_tasks.task_id = tasks.task_id
                JOIN employees ON employees_tasks.employee_id = employees.employee_id
            WHERE tasks.task_is_completed = 0
            GROUP BY employees.employee_id
    ),
    -- Common Table Expression 3 - Total Tasks per Employee
    employee_total_tasks AS (
        SELECT employees.employee_id,count() AS ttc
            FROM employees_tasks
                JOIN tasks ON employees_tasks.task_id = tasks.task_id
                JOIN employees ON employees_tasks.employee_id = employees.employee_id
            GROUP BY employees.employee_id
    )
    SELECT employees.employee_name, 
        CASE WHEN atr IS NOT NULL THEN atr ELSE 0 END AS average_completed_task_rating,
        CASE WHEN itc IS NOT NULL THEN itc ELSE 0 END AS incomplete_task_count,
        CASE WHEN ttc IS NOT NULL THEN ttc ELSE 0 END AS total_task_count
        FROM employees 
            LEFT JOIN employee_completedtask_info ON employees.employee_id = employee_completedtask_info.employee_id
            LEFT JOIN employee_notcompleted_info ON employees.employee_id = employee_notcompleted_info.employee_id
            LEFT JOIN employee_total_tasks ON employees.employee_id = employee_total_tasks.employee_id
    ;

based upon data generated as per the following :-

DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees (employee_id INTEGER PRIMARY KEY, department_id INTEGER, employee_name TEXT, employee_salary REAL,  employee_hire_date TEXT);
DROP TABLE IF EXISTS departments;
CREATE TABLE IF NOT EXISTS departments (department_id INTEGER PRIMARY KEY, department_name TEXT);
DROP TABLE IF EXISTS employees_tasks;
CREATE TABLE IF NOT EXISTS employees_tasks (employee_id INTEGER, task_id INTEGER, PRIMARY KEY(employee_id, task_id));

INSERT INTO departments VALUES
    (null,'Maths'),(null,'English'),(null,'Craft')
;
INSERT INTO employees VALUES
    (null,1,'Fred',55000,'2000-01-02'),
    (null,2,'Mary',62000,'1996-03-20'),
    (null,3,'Tom',52000,'2004-10-11'),
    (null,3,'Susan',72000,'1999-06-14'),
    (null,2,'Bert',66000,'2000-10-15'),
    (null,1,'Jane',70000,'1992-04-02')
;
INSERT INTO tasks VALUES
    (null,3,'Task 001 - Craft','Do the Craft thinggy','2018-01-01','2018-08-19',10,0),
    (null,1,'Task 002 - Maths','Do the Maths thinggy','2018-03-14','2019-03-13',20,0),
    (null,2,'Task 003 - English','Do the English thinggy','2018-02-14','2018-09-14',8,0),
    (null,3,'Task 004 - Craft','Do the Craft job','2018-01-01','2018-08-19',10,1),
    (null,1,'Task 005 - Maths','Do the Maths job','2018-03-14','2019-03-13',20,1),
    (null,2,'Task 006 - English','Do the English job','2018-02-14','2018-09-14',8,1),
    (null,3,'Task 007 - Craft','Craft thinggy','2018-03-03','2018-11-21',10,0),
    (null,1,'Task 008 - Maths','Maths thinggy','2018-03-14','2019-03-13',20,0),
    (null,2,'Task 009 - English','English thinggy','2018-02-14','2018-09-14',8,0)
;
INSERT INTO employees_tasks VALUES
    (1,2),(1,5),(1,8),(1,6),
    (2,2),
  (3,1),(3,4),(3,7)
;

This results in :-

• Note This converts null entries to 0's (i.e. in the above there are no tasks for Susan, Bert and Jane so nulls for their task counts/averages, which complicates matters a little hence the CASE WHEN ... THEN ... ELSE .... END AS clauses).
• Note I've included the total tasks counts as this may be useful/wanted (the third CTE extracts this info)