1

I have 2 tables User and Role, RoleId is a foreign key in User table, so I wrote a method here but it is not returning the output I want. I want to count both how many User for example have RoleId = 1 and how many have RoleId = 2:

public int countPersonelAdminandWorker() throws PerdoruesiException {
    Query query = em.createNativeQuery("SELECT COUNT(RoleID) FROM User p WHERE p.roleID = 1 ");

    try {
        return query.getSingleResut();
    }
    catch(NoResultException e) {
        throw new PerdoruesiException("...");
    }
}
Improve this question
1
  • 2
    Google for group by Commented May 12, 2020 at 4:23

2 Answers 2

Reset to default
3

"SELECT COUNT(RoleID) as 'Count Role ID' , roleID FROM User p WHERE p.roleID = 1 OR p.roleID=2 GROUP BY p.roleID";

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

6 Comments

jobayserozib , the code wont work if you want to count both of RoleId 1 and RoleId 2.
Why ? Could you please explain.
Your code is fing but it will count Users that have RoleId1 or RoleId 2 , the point was to count how many User have RoleID1 and how many have RoleId2.
That's why i use GROUP BY This will separate RoleID1 and RoleID2
@Dreni: how is your answer better than this one, as this is the much more standard and robust way to solve the original poster's problem?
|
2

Create a method that returns array.

public int[] countPersonelAdminandWorker() throws PerdoruesiException{
        Query query = em.createNativeQuery("SELECT COUNT(RoleID) from User p where p.roleID = 1 ");
        Query queryForWorker = em.createNativeQuery("SELECT COUNT(RoleID) from User p where p.roleID = 2");

        try{
            return new int[]{(int)query.getSingleResult(),(int)queryForWorker.getSingleResult()};
        }catch(NoResultException e){
            throw new PerdoruesiException("...");
        }


    }
Improve this answer

1 Comment

Are you and "TheFlash" one and the same person?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.