Page 1 of 1

why group function is not allowed in group by clause

PostPosted: Sun Dec 20, 2009 5:05 pm
by gvs
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 )

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

PostPosted: Mon Dec 21, 2009 12:23 am
by dick scherrer
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.

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

PostPosted: Mon Dec 21, 2009 8:20 pm
by gvs
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

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

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

How to fix this.

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

PostPosted: Tue Dec 22, 2009 1:35 am
by dick scherrer
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).

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

PostPosted: Mon Dec 28, 2009 8:37 pm
by GuyC
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