Re: Ask for help.

[Bob Buford <bbuford@CCCI.ORG>]

Hi,

GROUP BY is for aggregate functions (count, sum, average, etc.) only.  You can only list the column(s) you are grouping by and any aggregate functions; no other columns may be included in the "SELECT".


  e.g.
select
B.CUST_ID,
/*U.NAME1,                              !not allowed; not a group by column or an aggregate function     */
SUM(B.BAL_AMT),                 /*aggregate function - ok here    */
/*B.ITEM,                               !not allowed; not a group by column or an aggregate function     */
/*B.ITEM_LINE,          !not allowed; not a group by column or an aggregate function     */
/*B.CURRENCY_CD,        !not allowed; not a group by column or an aggregate function     */
/*B.DUE_DT,             !not allowed; not a group by column or an aggregate function     */
/*B.ACCOUNTING_DT,      !not allowed; not a group by column or an aggregate function     */
/*B.ASOF_DT,            !not allowed; not a group by column or an aggregate function     */
/*CC.CR_LIMIT,          !not allowed; not a group by column or an aggregate function     */
/*B.BAL_CURRENCY        !not allowed; not a group by column or an aggregate function     */

FROM
    [snip]

WHERE
  [snip]
GROUP BY
 B.CUST_ID

Note that you can group by several columns (if that logically fits what you want to do) and list each of those columns in the select, along with the aggregate functions.

HTH,
Bob

>>> Ngo Bao Chau <baochaun@MAIL.TRANSCONTINENTAL.CA> 11/12 5:01 PM >>>
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