Count Different Values in Oracle Using Outer Joins

My question is about left outer join with counter. I have two tables.

employee:
empid empname
----- -------
  101 Tom
  102 Jerry
  103 Jack 
  104 Tim 

allocation:
generator analyzer tester
--------- -------- ------
     101       102    103
     103       102    101
     102       101    104

I need following result

empid  empname generator analyzer  tester
------ ------- --------- -------- -------
101    Tom             1        1       1
102    Jerry           1        2       0
103    Jack            1        0       1
104    Tim             0        0       1

I need to count the values of each task. Tom has generated 1, analyzed 1 and tested 1. Like that i need to count the values. Is this possible in SQL. If this is possible please help me to get output.

I am getting the result. but it is not expected. I have used outer joins to solve the problem,

select  
    e.empid,
    e.empname,
    count(a1.generator),
    count(a2.analyzer),
    count(a3.tester)
from employee e 
    left join allocation a1 
        on e.empid=a1.author
    left join allocation a2 
        on e.empid=a2.reviewer
    left join allocation a3 
        on e.empid=a3.tester 
group by 
    e.empid,
    e.empname;

2 answers

  • answered 2018-04-17 04:21 Fact

    You can achieve it with this:

    Select empid,sum(generaor),sum(analyzer),sum(tester)
    from 
    (
    Select empid,count(a.generaor) generaor,0 analyzer,0 tester
    from employee    
    JOIN Allocation a on empid=a.generaor group by empid,a.generaor
    UNION 
    Select empid,0 generaor,count(b.analyzer)analyzer,0 tester
    from employee  
    JOIN Allocation b on empid=b.analyzer  group by empid,b.analyzer
    UNION 
    Select empid,0 generaor,0 analyzer,count(c.tester) tester
    from employee    
    JOIN Allocation c on empid=c.tester  group by empid,c.tester
    ) stag
    group by empid
    

  • answered 2018-04-17 04:21 bingi

    Try this

    SELECT empid, empname, sum(generator), sum(analyzer), sum(tester) FROM
    (SELECT e.empid, e.empname, count(a1.generator) generator, 0 analyzer, 0 tester
    FROM Employee e 
    JOIN Allocation a1 ON a1.generator = e.empid 
    GROUP BY e.empname
    UNION 
    SELECT e.empid, e.empname, 0 generator, count(a2.analyzer) analyzer, 0 tester
    FROM Employee e 
    JOIN Allocation a2 ON a2.analyzer = e.empid 
    GROUP BY e.empname
    UNION 
    SELECT e.empid, e.empname, 0 generator, 0 analyzer, count(a3.tester) tester
    FROM Employee e 
    JOIN Allocation a3 ON a3.tester = e.empid
    GROUP BY e.empname) tmp
    GROUP BY empname 
    ORDER BY empid