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

Question

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

What is your current query? Also, what product are you using; MySQL or SQL Server? — Rich Benner
– Rich Benner, Commented Jun 26, 2017 at 13:53
You shouldn't post code in a comment anyway (and welcome to SO). — HoneyBadger
– HoneyBadger, Commented Jun 26, 2017 at 14:11
Slight detour....you should start using ANSI-92 style joins. They are easier to maintain and less prone to error. They have been around more than 25 years now. sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Sean Lange
– Sean Lange, Commented Jun 26, 2017 at 14:17

HoneyBadger · Accepted Answer · 2017-06-26 14:38:03Z

2

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.

edited Jun 26, 2017 at 14:38

answered Jun 26, 2017 at 14:21

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thorsten Kettner Over a year ago

I'd use COUNT instead of SUM or add ELSE 0 for SUM to get zero counts rather than null.

HoneyBadger Over a year ago

@ThorstenKettner, good point. At first I used COUNT, then I thought SUM might make it a bit more intuitive (perhaps wrongly anyway). Forgot about nulls in the edit. Edited now.

Elad L. · Accepted Answer · 2017-06-26 14:12:41Z

1

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

answered Jun 26, 2017 at 14:12

Elad L.

6491 gold badge10 silver badges26 bronze badges

1 Comment

No-one Over a year ago

Thank you this worked for me with minor adjustment. Appreciate your time and help.

V. Courtois · Accepted Answer · 2017-06-26 13:57:28Z

0

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

answered Jun 26, 2017 at 13:57

V. Courtois

3428 silver badges21 bronze badges

3 Comments

No-one Over a year ago

I cannot created views. the ultimate goal is to put it in behind a GUI that uses pure sql to show results in user readable formats. (e.g. Jasper)/

V. Courtois Over a year ago

I see ; then you can go for the dirty subqueries :)

HoneyBadger Over a year ago

Subqueries are neither dirty nor necessary here (in all likelihood).

Elad L. · Accepted Answer · 2017-06-26 14:03:14Z

0

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]

answered Jun 26, 2017 at 14:03

Elad L.

6491 gold badge10 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related