complex sql query with jpa jpql

Question

I use JPA2 for the dao layer.

Suppose i have a table as follows (just a sample):

emp_name     week     approver_name  status   hours
emp1       2010-01-02    app1          a        2
emp1       2010-01-02    app1          a        2
emp1       2010-01-02    app2          b        3
emp1       2010-01-09    app1          b        2
emp1       2010-01-09    app2          a        7
emp2       2010-01-02    app1          b        5
emp2       2010-01-02    app2          a        9
emp2       2010-01-02    app1          a        3
emp2       2010-01-09    app2          a        4
emp2       2010-01-09    app2          b        7
emp2       2010-01-09    app1          a        3
emp2       2010-01-09    app1          a        2

according to the give tables, (actually more complex than this), how can i get the result like this

emp_name     week     approver_name    status  hours (add hours together)
emp1      2010-01-02      app1          a        4
emp1      2010-01-02      app2          b        3
emp1      2010-01-09      app1          b        2
emp1      2010-01-09      app2          a        7
emp2      2010-01-02      app1          b        5
emp2      2010-01-02      app1          a        3
emp2      2010-01-02      app2          a        9
emp2      2010-01-09      app1          a        5
emp2      2010-01-09      app2          a        4
emp2      2010-01-09      app2          b        7

NB: the status also must be distinguished. also the hours column is not existed in the db, the column is start_time and end_time

so anyone can have some good idea for that? I can' group by any column, because it will mix result.

thanks for that.

Answer 1

Try this:

SELECT emp_name, week, approver_name, status, SUM(hours)
FROM MyTable
GROUP BY emp_name, week, approver_name, status
ORDER BY emp_name, week, approver_name, status