Re: Ask for help.

[Tony DeLia <tdelia@EROLS.COM>]

The SUM statement doesn't need to be at the beginning of the select
list. The only requirement is any non-aggregate columns in your select
list must be present in your group by list...

SELECT aaa, bbb, ccc, SUM(ddd), COUNT(*)
  FROM ...
 GROUP BY aaa, bbb, ccc

Column placement is up to the user... it's merely a position...

                              -Tony DeLia

Tom Hippensteel wrote:
>
> Some databases may let you use a single input in a SQL SELECT, but I believe
> Oracle (I assume Oracle from the error message) requires all inputs
> contained in the "group by" statement, with the SUM at the top of the input
> list.  You can also attach an "order by" statement after the "group by"
> statement if you need the output in a certain order.
>
> Like so:
>
> select
> SUM(B.BAL_AMT),
> B.CUST_ID,
> U.NAME1,
> B.ITEM,
> B.ITEM_LINE,
> B.CURRENCY_CD,
> B.DUE_DT,
> B.ACCOUNTING_DT,
> B.ASOF_DT,
> CC.CR_LIMIT,
> B.BAL_CURRENCY
>
> FROM
>   PS_ITEM B,
>   PS_CUSTOMER U,
>   PS_CUST_OPTION CO,
>   PS_CUST_CREDIT CC,
>   PS_SET_CNTRL_REC V,
>   PS_BUS_UNIT_TBL_FS L
>
> WHERE
>   B.CUST_ID = U.CUST_ID AND
>   B.BUSINESS_UNIT = L.BUSINESS_UNIT AND
>   U.SETID = V.SETID AND
>   V.SETCNTRLVALUE = B.BUSINESS_UNIT AND
>   CO.EFFDT = (SELECT MAX(COA.EFFDT)
>                       FROM PS_CUST_OPTION COA
>                       WHERE CO.SETID = COA.SETID
>                         AND CO.CUST_ID = COA.CUST_ID
>                           AND COA.EFFDT <= sysdate) AND
>   CO.SETID = U.SETID AND
>   CO.CUST_ID = U.CUST_ID AND
>   CO.DST_ID_AR = 'AR_EXT' AND
>   CC.EFFDT =(SELECT MAX(EFFDT) FROM PS_CUST_CREDIT
>                      WHERE CC.SETID = SETID
>                          AND CC.CUST_ID = CUST_ID
>                          AND EFFDT <= sysdate) AND
>   U.SETID = CC.SETID AND
>   U.CUST_ID = CC.CUST_ID AND
>   V.RECNAME = 'CUSTOMER'
> GROUP BY
>  B.CUST_ID,
>  U.NAME1,
>  B.ITEM,
>  B.ITEM_LINE,
>  B.CURRENCY_CD,
>  B.DUE_DT,
>  B.ACCOUNTING_DT,
>  B.ASOF_DT,
>  CC.CR_LIMIT,
>  B.BAL_CURRENCY
>
> > -----Original Message-----
> > From: baochaun@mail.transcontinental.ca
> > [SMTP:baochaun@mail.transcontinental.ca]
> > Sent: Thursday, November 12, 1998 4:01 PM
> > To:   'SQR-USERS@USA.NET'
> > Subject:      Ask for help.
> >
> > Hi everybody
> >
> >       I have problem with my select, if every body could help me, here is
> > my select, here is the error ORA-00937 NOT A SINGLE-GROUP GROUP FUNCTION.
> > In this select, I want to have a total of b.bal_amt only on cust_id.  How
> > can fix it?
> >
> >       Thank you for your help.
> >
> > select
> > B.CUST_ID,
> > U.NAME1,
> > SUM(B.BAL_AMT),
> > B.ITEM,
> > B.ITEM_LINE,
> > B.CURRENCY_CD,
> > B.DUE_DT,
> > B.ACCOUNTING_DT,
> > B.ASOF_DT,
> > CC.CR_LIMIT,
> > B.BAL_CURRENCY
> >
> > FROM
> >   PS_ITEM B,
> >   PS_CUSTOMER U,
> >   PS_CUST_OPTION CO,
> >   PS_CUST_CREDIT CC,
> >   PS_SET_CNTRL_REC V,
> >   PS_BUS_UNIT_TBL_FS L
> >
> > WHERE
> >   B.CUST_ID = U.CUST_ID AND
> >   B.BUSINESS_UNIT = L.BUSINESS_UNIT AND
> >   U.SETID = V.SETID AND
> >   V.SETCNTRLVALUE = B.BUSINESS_UNIT AND
> >   CO.EFFDT = (SELECT MAX(COA.EFFDT)
> >                     FROM PS_CUST_OPTION COA
> >                     WHERE CO.SETID = COA.SETID
> >                       AND CO.CUST_ID = COA.CUST_ID
> >                         AND COA.EFFDT <= sysdate) AND
> >   CO.SETID = U.SETID AND
> >   CO.CUST_ID = U.CUST_ID AND
> >   CO.DST_ID_AR = 'AR_EXT' AND
> >   CC.EFFDT =(SELECT MAX(EFFDT) FROM PS_CUST_CREDIT
> >                    WHERE CC.SETID = SETID
> >                        AND CC.CUST_ID = CUST_ID
> >                        AND EFFDT <= sysdate) AND
> >   U.SETID = CC.SETID AND
> >   U.CUST_ID = CC.CUST_ID AND
> >   V.RECNAME = 'CUSTOMER'
> > GROUP BY
> >  B.CUST_ID
> >
> > Merci
> >
> > Ngo Baochau
> > baochaun@mail.transcontinental.ca
> > tel: (514) 335 - 1466/4943
> > http://www.transcontinental.ca

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com