Question & Answer > View Question

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

Confluence User - 28 Nov, 2018

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
id	departmentId	unitId	id	unitId	subUnitId

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 name	Members in Dept	Units in Dept	Sub-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

datalists;reports

2

Confluence User •

29 Nov, 2018 •

confluenceUser •

1 •

confluenceUser •

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

Confluence User •

29 Nov, 2018 •

confluenceUser •

•

confluenceUser •

This is exactly what i need. Thanks @Matthew.

1

CSV | Excel | XML | PDF

Your answer

Print

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

2

RELATED QUESTIONS

Your answer

To answer a question you'll need an account.

2.builder-style-4d220979-dd45-4ba5-8442-27446fd3a244{color:#008C60 !important;}

RELATED QUESTIONS

Your answer.builder-style-92d484a2-2d7e-4baa-bdc0-2d880d17c153{color:#008C60 !important; margin-left:10px !important;}

To answer a question you'll need an account.

2

Your answer