CODEWITHSUNDEEP
sqloracle12c
No-one
No-one

Reputation: 43

count after join on multiple tables and count of multiple column values

Please help me with below problem.

table 1 employee details

emp name          empno.
---------------------------------
John               1234

Joe                6789

table 2 employee assignment

empno  assignmentstartdate assignmentenddate assignmentID  empassignmentID
-----------------------------------------------------------------------------
1234      01JAN2017            02JAN2017         A1            X1

6789      01jan2017            02JAN2017         B1            Z1

table 3 employee assignment property

empassignmentID   assignmentID  propertyname        propertyvalue
-------------------------------------------------------------------
X1                  A1           COMPLETED           true

X1                  A1           STARTED             true

Z1                  B1           STARTED             true

Z1                  B1           COMPLETED           false

Result wanted: (count of completed and started for each employee)

emp name   emp no.  COMPLETED   STARTED
------------------------------------------
John       1234      1           1

Joe        6789      0           1

Currently with my query it is not putting count correctly for propertyvalue if I run for one employee it works correctly but not for multiple employees. Please help. 

 SELECT empno ,
  empname     ,
 (SELECT COUNT(A.propertyvalue)
 FROM employeedetails C ,
 employees_ASSIGNMENT RCA,
 employee_assignment_property A
 WHERE TRUNC(startdate) >= '14jun2017'
 AND TRUNC(endate)        <= '20jun2017'
 AND RCA.empno             = C.empno
 AND RCA.empassignmetid    = A.empassignmetid
 AND rca.EMPNO            IN ('1234','6789')
  AND RCA.assignmentid      = A.assignmentid
  AND A.Name                = 'COMPLETED'
  AND A.propertyvalue       = 'true') ,
  (SELECT COUNT(A.propertyvalue)
   FROM employeedetails C ,
   employees_ASSIGNMENT RCA,
  employee_assignment_property A
  WHERE TRUNC(startdate) >= '14jun2017'
  AND TRUNC(endate)        <= '20jun2017'
  AND RCA.empno             = C.empno
  AND RCA.empassignmetid    = A.empassignmetid
 AND rca.EMPNO            IN ('1234','6789')
 AND RCA.assignmentid      = A.assignmentid
 AND A.Name                = 'STARTED'
 AND A.propertyvalue       = 'true')FROM employeedetails  WHERE EMPNO IN 
 ('1234','6789') GROUP BY C.empno ,
  C.EMPNAME

Upvotes: 4

Views: 1125

Answers (4)

HoneyBadger
HoneyBadger

Reputation: 15150

I think you are simply looking for this:

SELECT      DET.empname     
,           COUNT(CASE WHEN PROP.propertyname = 'COMPLETED' THEN 1 END) COMP_COUNT
,           COUNT(CASE WHEN PROP.propertyname = 'STARTED' THEN 1 END) START_COUNT
FROM        employeedetails DET
INNER JOIN  employees_ASSIGNMENT ASS
        ON  ASS.empno = DET.empno
INNER JOIN  employee_assignment_property PROP
        ON  PROP.empassignmentID = ASS.empassignmentID   
        AND PROP.assignmentID  = ASS.assignmentID
GROUP BY    DET.empname

Just add a WHERE clause if you need one.

Upvotes: 2

Elad L.
Elad L.

Reputation: 639

if you want you result as a query without CTEs this should work:

  select empName,
       empNo,   
        (select employee_details.empNo, count(employee_assignment.assId) 
        from employee_details as t1
        join employee_assignment on (t1.empno = employee_assignment.empno)
        join employee_assignment_property on (employee_assignment.assId = employee_assignment_property.assId)
        where employee_assignment.ptop = 'COMPLETED'
                and t.empNo = t1.empNo
        group by t1.empNo ) as [COMPLETED],

        (select employee_details.empNo, count(employee_assignment.assId) 
        from employee_details as t1
        join employee_assignment on (t1.empno = employee_assignment.empno)
        join employee_assignment_property on (employee_assignment.assId = employee_assignment_property.assId)
        where employee_assignment.ptop = 'STARTED'
                and t.empNo = t1.empNo
        group by t1.empNo ) as [STARTED],       
from employee_details as t

Upvotes: 1

Elad L.
Elad L.

Reputation: 639

this should work using CTEs: Using Common Table Expressions

with numComplet()
as 
(
select tbl1.empNo, count(tbl2.assId) 
from tbl1 
join tbl2 on (tbl1.empno = tbl2.empno)
join tbl3 on (tbl2.assId = tbl3.assId)
where tbl2.ptop = 'COMPLETED'
group by tbl1.empNo
),


with numStarted()
as 
(
select tbl1.empNo, count(tbl2.assId) 
from tbl1 
join tbl2 on (tbl1.empno = tbl2.empno)
join tbl3 on (tbl2.assId = tbl3.assId)
where tbl2.ptop = 'STARTED'
group by tbl1.empNo
)

select * 
from tbl1 
join numComplet on (tbl1.empNo = numComplet.empNo)
join numStarted on (tbl1.empNo = numStarted.empNo)

I put down table names as tbl[1|2|3]

Upvotes: 0

V. Courtois
V. Courtois

Reputation: 342

If you don't want to do a dirty query composed of subqueries, you can try creating a view (if your database permits it).

What does it mean : I'll be useless in front of this. In summary, a view is a temporary table.

Hope this helps

Upvotes: 0

Related Questions