why group function is not allowed in group by clause



IBM's flagship relational database management system

why group function is not allowed in group by clause

Postby 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 )
gvs
 
Posts: 2
Joined: Sun Dec 20, 2009 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby gvs » Mon Dec 21, 2009 8:20 pm

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.
gvs
 
Posts: 2
Joined: Sun Dec 20, 2009 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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).
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby 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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post