Does count(sum()) function work in ibm iseries navigator ?

[erebus]

Consider an Employee table with columns employee id , employee name, salary, department name and department number.

We need to find “count” of the aggregate function, say sum of salary, grouped by department name and employee name !!!

The following query
"Select count(sum(salary)) from employee where ....(some condition) group by department name, employee name".
fails in IBM iseries navigator . Also I tried with inline views, sub queries, etc. ;but in vain;

[docdiesel]

Hi,

count(sum()) won't work, like this example with sysibm.sydummy1 shows:

 db2 "select sum(7) from sysibm.sysdummy1"
1
-----------
          7
  1 record(s) selected.
 
db2svc01@iona:~> db2 "select count(sum(7)) from sysibm.sysdummy1"
SQL0112N  The operand of the column function "COUNT" includes a column
function, a scalar fullselect, or a subquery.  SQLSTATE=42607

SUM() always returns a number, so its count would be 1. I'm not quiet sure if I understand your needs correctly, but I think you don't need to count the sums but the departments. So you might need something like this:

SELECT  department, sum(salary), count(distinct employee)
FROM    myschema.myemployees
GROUPED BY department

Regards, Bernd