IBM Mainframe Forum

by **gvs** » Sun Dec 20, 2009 5:05 pm

Hello,
i want to know why group function is not allowed in group by clause.
select to_char(hiredate,'yyyy')
from emp
group by to_char(hiredate,'yyyy');
I need equivalent query in db2 .(to select recruited year in a organisation )

by **dick scherrer** » Mon Dec 21, 2009 12:23 am

Hello and welcome to the forum,

You have not specified anything to "group". . . What do you want as output from this query? As posted it makes no sense. . .

Suggest you spend some time learning the rules of GROUP BY. . .
http://publibz.boulder.ibm.com/cgi-bin/ ... pj10/1.1.6

If you post a few sample input rows from the table and "output" you want, someone should be able to offer a more usable suggestion.

by **gvs** » Mon Dec 21, 2009 8:20 pm

Select all

select deptno,sum(sal),sum(sal) / e.totsal * 100 as deptpercent
from emp,(select sum(sal) totsal
from emp ) e
group by deptno,e.totsal

Select all

OUTPUT:
DEPTNO SUM(SAL) DEPTPERCENT
---------- ---------- -------------------
10 8750 30.1464255
20 10875 37.4677003
30 9400 32.3858742

in the query iam caluculating department wise investments.But in db2 Output is something like

Select all

DEPTNO SUM(SAL) DEPTPERCENT
---------- ---------- -------------------
10 8750 0
20 10875 0
30 9400 0

How to fix this.

by **dick scherrer** » Tue Dec 22, 2009 1:35 am

Hello,

Note - your info has been "Code'd" - you should practice with the bbTags as they greatly improve readability.

Is this related to the original "group by" question somehow?

If the issue now is the zeros in the % field, suggest you look at DECimal and ROUND. You might also just change the 100 to 100.000 (i'm not connected just now and so this is not tested).

by **GuyC** » Mon Dec 28, 2009 8:37 pm

question 1 :
select year(hiredate),count(*) from emp group by year(hiredate)
should work

(@dick you don't need a count(*) or sum() then a group by works like a distinct)

question 2 :
like dick said : integer / integer * integer = (integer / integer ) * integer = integer
easiest way would be : 100.000000 * sal / totsal

IBM Mainframe Forum

why group function is not allowed in group by clause

why group function is not allowed in group by clause

Re: why group function is not allowed in group by clause

Re: why group function is not allowed in group by clause

Re: why group function is not allowed in group by clause

Re: why group function is not allowed in group by clause