how to reference multiple columns in a 1:N relationship chain

I'm still in my learning curve with grails and I've bumped into the following problem:

class Departement {
String  name    // "hr", "it"...

static hasMany = [bureaus:Office]
}


class Office {
String  bureaunumber    // 102, 104, 106...

static hasMany = [ppl:Personnel]
}


class Personnel {
String  name    // "Smith", "Walker"...
String  title   // "project manager", "ceo", "financial manager"...
Integer salary
}

The goal is to find sum of salary for all managers in a specific departement. The result must deliver the total of salary, the total number of managers and the name of department something like this:

|dept    |total manager|wages   |
|--------|-------------|--------|
| hr     | 4           | 340000 |
| it     | 7           | 610400 |
| ...    | ...         | ...    |
| all    | 11          | 950400 |

etc.

I'd like to create a resultset with the hibernate criteria api but I'm stuck with the SQL query that would deliver the results from above. Currently, it gives me the proper sum of salary:

select sum(salary) wages
from personnel
where title LIKE '%manager%'
  and office_id in
  (
    select office.id
    from office
    where office.dept_id in
    (
      select dept.id
      from dept
      where name = 'hr'
    )
  )

and well - unfortunately that's all sofar. Does anybody have an idea how to count the total number of managers for each department and how to add the department name to the resultset?

Any input would be much appreciated!

=========================

Hi guys, thank you for taking the time answering my question!

I'm a bit confused by the count(managers) part of the suggested solution of Ben, I have no managers field in the personnel domain class... do I miss something there?

Tried with count(*) and also with count(personnel.name) but it gives me a total of salary in each and every department of all employees, also, it adds up the total count of personnel - and not just those with "manage开发者_StackOverflow社区r" title :-/

so the result after running a query looks like this:

|dept    |total manager|wages    |
|--------|-------------|---------|
| hr     | 139         | 3988800 |
| it     | 139         | 3988800 |
| ...    | 139         | 3988800 |
| all    | 139         | 3988800 |

======== update 2: sql server 2005+ syntax to oracle ===========

SELECT
   CASE GROUPING(d.name)
     WHEN 1 THEN 'All:'
     ELSE d.name
   END dept,
   COUNT(*) total_manager,
   SUM(p.salary) wages
FROM departement d
   INNER JOIN office o ON d.id = o.dept_id
   INNER JOIN personnel p ON o.id = p.office_id
WHERE p.name LIKE '%manager%'
GROUP BY ROLLUP(d.name)

In SQL Server 2005+

SELECT
  CASE GROUPING(d.name)
    WHEN 1 THEN 'All:'
    ELSE d.name
  END AS dept,
  COUNT(*) AS total_manager,
  SUM(p.salary) AS wages
FROM dept d
  INNER JOIN office o ON d.id = o.dept_id
  INNER JOIN personnel p ON o.id = p.office_id
GROUP BY d.name WITH ROLLUP

I think you are close. When using SUM and COUNT you sometimes need a GROUP BY clause in your SQL, so maybe that's what you are lacking. Something like:

SELECT  dept,
        COUNT(managers),
        sum(salary) wages
FROM personal
WHERE title LIKE '%manager%'
AND office_id IN 
    (select office.id from office where office.dept_id in (select dept.id from dept where name = 'hr'))
GROUP BY managers