Get Total male or Female count of a State Group by State Name In OQL

1
I want to make Query like this   SELECT StateObj.StateName,(SELECT COUNT(tab1.id FROM Employee as tab1 WHERE tab1.gender=’Male’ AND tab1.employee_state = StateObj.id )) as totalMaleEmployee FROM State AS StateObj INNER JOIN Employee As E ON E.employee_state = StateObj.id GROUP By StateObj.StateName     i want to create above query in mendix OQL  which provide me Male (or Female) Count Of Employee Group by State Name   Please Share a Solution     SELECT StateObj.StateName StateName, (SELECT COUNT(te/ID) FROM MyFirstModule.Employee te WHERE te.Gender='Male') AS totalMaleEmployee ,(SELECT COUNT(te/ID) FROM MyFirstModule.Employee te WHERE te.Gender='Female') AS totalFemaleEmployee FROM MyFirstModule.State AS StateObj INNER JOIN StateObj/MyFirstModule.Employee_State/MyFirstModule.Employee e GROUP BY StateObj/StateName    
asked
3 answers
0

hii reena this is solution of your query

 

 

SELECT StateObj.StateName StateName, (SELECT COUNT(te/ID) FROM MyFirstModule.Employee te WHERE te.Gender='Male' And te/MyFirstModule.Employee_State/MyFirstModule.State/StateName = StateObj.StateName) AS totalMaleEmployee ,(SELECT COUNT(te/ID) FROM MyFirstModule.Employee te WHERE te.Gender='Female' And te/MyFirstModule.Employee_State/MyFirstModule.State/StateName=StateObj.StateName) AS totalFemaleEmployee FROM MyFirstModule.State AS StateObj INNER JOIN StateObj/MyFirstModule.Employee_State/MyFirstModule.Employee e GROUP BY StateObj/StateName

answered
0

Hi Reena,

If you want to use OQL within Mendix, you can definitely do so. Check out

https://docs.mendix.com/refguide/oql/ Documentation

https://service.mendixcloud.com/p/OQL  Playground / Demo

https://marketplace.mendix.com/link/component/66876 Marketplace module

answered
0

Maybe this:

SELECT StateObj.StateName StateName,
(SUM(CASE WHEN e.Gender='Male' THEN 1 ELSE 0 END)) AS totalMaleEmployee,
(SUM(CASE WHEN e.Gender='Female' THEN 1 ELSE 0 END)) AS totalFemaleEmployee,
FROM MyFirstModule.State AS StateObj
INNER JOIN StateObj/MyFirstModule.Employee_State/MyFirstModule.Employee e
GROUP BY StateObj/StateName

 

answered