how do i count number of people in department and other groups

 
1
0
-1

Hi Experts,

I have a requirements for tracking people in

In my app, i have departments, units and sub-units. I use the Joget department for departments and groups for the units and sub-units. I have a table that lists groups against departments and one that lists sub-units against units as shown below.

Table A  Table B
iddepartmentIdunitId  idunitIdsubUnitId
        

 

I now need to generate a report showing the number of people in a department, the number of units in the department and the number of sub-units in the department as shown below

Department nameMembers in DeptUnits in DeptSub-Units in Dept
    

the following SQL gives me incorrect results.

SELECT 
a.id,
a.name,
count(b.departmentId) AS members,
COUNT(c.c_deptId) AS unit,
COUNT(e.c_unitId) AS subunit

FROM dir_department a 
left join dir_employment b on b.departmentId = a.id
left join app_fd_cap_units c on c.c_deptId= b.departmentId
left join app_fd_cap_subunits e on e.c_unitId= c.c_unitId


where a.organizationId ="dcphc"


group by a.id
order by members

can anyone help

    CommentAdd your comment...

    1 answer

    1.  
      2
      1
      0

      Hi

      I think you might need to use subquery to individually count the unit and sub_units, example:

      SELECT d.id AS dept_id,
          d.name,
          COUNT(d.id) AS totalDept,
          (SELECT COUNT(*) FROM app_fd_units u
          WHERE u.c_deptId = d.id ) AS unitTotal,
          (SELECT COUNT(*) FROM app_fd_subunits su
          LEFT JOIN app_fd_units u ON su.c_unitId = u.id
          WHERE u.c_deptId=d.id) AS subUnitTotal
      FROM dir_department d
      GROUP BY d.id

      Do try this attached demo app that gives a report count as per your requirement, hopefully it works correctly.
      App was designed for Joget Workflow Enterprise/Professional v6 and for MySQL database.

      APP_33096354_count_dept_units.jwa

      Cheers

      1. Chiek

        This is exactly what i need. Thanks @Matthew.

      CommentAdd your comment...