Topic
3 replies Latest Post - ‏2011-06-27T17:36:08Z by SystemAdmin
cmptrwhz
cmptrwhz
3 Posts
ACCEPTED ANSWER

Pinned topic sql grouping help

‏2011-05-14T20:36:54Z |
I am using an odbc connection to an as400, I know the connection string is proper and working as all my other queries work without any problem.

why is it that this sql statement works:


select rdqty, alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, ifnull(iecactno,actvty) as actlvl, class, ifnull(ieclp,lp) as cost from alib.rcvshpdt left outer join alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno left outer join alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc where alib.rcvshpdt.rcvno=
'3930697' order by actlvl asc


but as soon as I try to sum a quanity and group the results as the following it fails.


select sum(rdqty) as quanity, alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, ifnull(iecactno,actvty) as actlvl, class, ifnull(ieclp,lp) as cst from alib.rcvshpdt left outer join alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno left outer join alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc where alib.rcvshpdt.rcvno=
'3930697' group by alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, actlvl, class, cst order by actlvl asc
Updated on 2011-06-27T17:36:08Z at 2011-06-27T17:36:08Z by SystemAdmin
  • cmptrwhz
    cmptrwhz
    3 Posts
    ACCEPTED ANSWER

    Re: sql grouping help

    ‏2011-05-14T21:54:06Z  in response to cmptrwhz
    just in case someone actually reads this I found the answer to be that you just need to use the calculations from the select rather than the labels as you cannot group by label names. Here is the full sql that works.

    
    select sum(rdqty) as quanity, alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, ifnull(iecactno,actvty) as actlvl, class, ifnull(ieclp,lp) as cst from alib.rcvshpdt left outer join alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno left outer join alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc where alib.rcvshpdt.rcvno=
    '3930697' and ifnull(iecactno,actvty) between 0 and 2 group by alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, ifnull(iecactno,actvty), class, ifnull(ieclp,lp) order by actlvl asc
    
  • cmptrwhz
    cmptrwhz
    3 Posts
    ACCEPTED ANSWER

    Re: sql grouping help

    ‏2011-05-14T21:55:56Z  in response to cmptrwhz
    the solution was that you cannot group by label name so just use the calculation within the select rather than the label name. I have posted the full sql statement for anyone looking for the same answers.
    • SystemAdmin
      SystemAdmin
      2364 Posts
      ACCEPTED ANSWER

      Re: sql grouping help

      ‏2011-06-27T17:36:08Z  in response to cmptrwhz

      I'm a little late to the party here, but I wanted to thank you for posting your solution, even though the question got no response.  I appreciate that you are willing to save the next person your own pain of discovery.

      Cheers!

      Chris Walden