By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

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

P: 6
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;
Sep 25 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 297

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

Expand|Select|Wrap|Line Numbers
  1.  db2 "select sum(7) from sysibm.sysdummy1"
  2. 1
  3. -----------
  4.           7
  5.   1 record(s) selected.
  7. db2svc01@iona:~> db2 "select count(sum(7)) from sysibm.sysdummy1"
  8. SQL0112N  The operand of the column function "COUNT" includes a column
  9. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT  department, sum(salary), count(distinct employee)
  2. FROM    myschema.myemployees
  3. GROUPED BY department
Regards, Bernd
Sep 25 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.