More SQL help needed - IBM AS400

This is a discussion on More SQL help needed - IBM AS400 ; Hello, In a previous post Elvis was so helpful in getting me to understand how to perform a SUM of a Field(s). Now when I do this I only get SS# and the SUM'd fields but I also need to ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: More SQL help needed

  1. More SQL help needed

    Hello,
    In a previous post Elvis was so helpful in getting me to understand
    how to perform a SUM of a Field(s). Now when I do this I only get SS#
    and the SUM'd fields but I also need to display the Name, Federal ID,
    Authorization, Signatory, Deposit#, Local and a Date. When I include
    these fields in my SELECT statement the SQL bombs on the Group By
    because I only have SS# in the Group By. As soon as I include all
    fields from the Select in the Group By it works, but I do not want to
    Group By all of those fields. Will this require 1 or 2 additional SQL
    statements? I'm not sure. I do not know where to go.


    TIA
    that 1 guy


  2. Re: More SQL help needed

    On 2007-02-15, that 1 guy wrote:
    > Hello,
    > In a previous post Elvis was so helpful in getting me to understand
    > how to perform a SUM of a Field(s). Now when I do this I only get SS#
    > and the SUM'd fields but I also need to display the Name, Federal ID,
    > Authorization, Signatory, Deposit#, Local and a Date. When I include
    > these fields in my SELECT statement the SQL bombs on the Group By
    > because I only have SS# in the Group By. As soon as I include all
    > fields from the Select in the Group By it works, but I do not want to
    > Group By all of those fields. Will this require 1 or 2 additional SQL
    > statements? I'm not sure. I do not know where to go.



    Elvis may have a better solution, but you can try a SQL statement
    in this format:

    with MYTEMP as
    ( select ssn,
    sum(fieldtobesummed) TheSum
    group by ssn )
    select ssn,
    TheSum,
    all those other fields
    where MYTEMP.ssn = REALFIELD.ssn


    --
    ---------------------------------------------------------------------------
    Julien Mills Director MIS
    Amoroso's Baking Company 215-471-4740
    Philadelphia, PA USA Fax 215-472-5299

  3. Re: More SQL help needed

    Julien has the right idea, but can "that 1 guy" tell us which of many
    SS# records from the REALTABLE he wants to join to the single record
    coming out of GROUP BY result set?
    Are the Name, Federal ID, Authorization, Signatory, Deposit#, Local
    and a Date always the same for that SS#?
    If they are you could just use other aggregate functions and pick any
    of them, i.e.:

    select hess,
    sum(hewag),min(theName),min(theFederalId),min(Auth orization),min(Signatory),min(Deposit#),min(Local) ,min(Date)
    from "LIUNAD01"/"DUHSTE"
    where ((hess = 316903160) and
    (hedtr > 20060000) and
    (hedtr < 20070000))
    group by hess

    Chances are those other fields are different in each record and that
    complicates the solution as you have to get more creative about the
    way you join back to the original table.
    For us to help you'll need to define exactly what you want to join on.
    Easiest thing from the forum perspective is to give us sample input
    rows and desired output you want.

    Elvis

    On Feb 15, 11:34 am, Julien Mills
    wrote:
    > On 2007-02-15, that 1 guy wrote:
    >
    > > Hello,
    > > In a previous post Elvis was so helpful in getting me to understand
    > > how to perform a SUM of a Field(s). Now when I do this I only get SS#
    > > and the SUM'd fields but I also need to display the Name, Federal ID,
    > > Authorization, Signatory, Deposit#, Local and a Date. When I include
    > > these fields in my SELECT statement the SQL bombs on the Group By
    > > because I only have SS# in the Group By. As soon as I include all
    > > fields from the Select in the Group By it works, but I do not want to
    > > Group By all of those fields. Will this require 1 or 2 additional SQL
    > > statements? I'm not sure. I do not know where to go.

    >
    > Elvis may have a better solution, but you can try a SQL statement
    > in this format:
    >
    > with MYTEMP as
    > ( select ssn,
    > sum(fieldtobesummed) TheSum
    > group by ssn )
    > select ssn,
    > TheSum,
    > all those other fields
    > where MYTEMP.ssn = REALFIELD.ssn
    >
    > --
    > ---------------------------------------------------------------------------
    > Julien Mills Director MIS
    > Amoroso's Baking Company 215-471-4740
    > Philadelphia, PA USA Fax 215-472-5299




  4. Re: More SQL help needed

    Julien/Elvis,
    Good stuff. The boss and I are currently discussing the results of
    your example Elvis. He was wanting me to try something new with the
    FETCH for this scenario using ":" program variables when I saw your
    reply, so I tried it and wanted to show him what the results were. I
    may need to post more and do thank all of your for your input.


    that 1 guy


  5. Re: More SQL help needed


    "that 1 guy" wrote in message
    news:1171557171.391503.128000@p10g2000cwp.googlegr oups.com...
    > Hello,
    > In a previous post Elvis was so helpful in getting me to understand
    > how to perform a SUM of a Field(s). Now when I do this I only get SS#
    > and the SUM'd fields but I also need to display the Name, Federal ID,
    > Authorization, Signatory, Deposit#, Local and a Date. When I include
    > these fields in my SELECT statement the SQL bombs on the Group By
    > because I only have SS# in the Group By. As soon as I include all
    > fields from the Select in the Group By it works, but I do not want to
    > Group By all of those fields. Will this require 1 or 2 additional SQL
    > statements? I'm not sure. I do not know where to go.


    You are barking up the right tree; all non-summed fields need to be set in
    the Group By. Chances are, all those fields are unique to the record as
    well (i.e. you shouldn't see multiple names associated with the same SSN),
    and you'll see just a single summed record per person.

    Dave



+ Reply to Thread