Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sql group by and sub-select problem

Re: sql group by and sub-select problem

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Fri, 22 Nov 2002 21:11:30 GMT
Message-ID: <3DDE9D7D.4049AA82@exesolutions.com>


Bill wrote:

> I need to select a column with a sub-select. One of the selected
> columns has a sum function and as a result of this a group by
> statement is required. How do I reference my sub-selected column in
> the group by statement? I can not select the column through a join
> because it will cause duplicates in my result set. Here is my sql
> which returns an invalid column name error:
>
> SELECT SUM(ARIBA_GL_AMT) RECCLUB,
> SUM(SENTTO_PAYROLL_AMT) PAYROLL,
> NULL, B.FC_NO, B.EMP_SS_NO, TO_NUMBER(NULL), GL_ACCT_CD,
> 'Chargeback', 2 sortby,
> (SELECT DISTINCT C.SUM_SORT FROM TB_FCC_ACCT C WHERE C.GL_ACCT_CD
> = A.GL_ACCT_CD)
> FROM TB_FCC_EMP B, TB_FCC_RECLASS A
> WHERE A.EMPID = '43629'
> AND A.OFFNO = '050'||B.FC_OFC_NO
> AND A.PERIOD >= 200201
> AND A.PERIOD <= 200210
> AND A.TRANS_TYPE IN ('AG','BP')
> AND B.EMP_ML_ID = A.EMPID
> HAVING SUM(ARIBA_GL_AMT) > 0 OR SUM(SENTTO_PAYROLL_AMT) > 0
> GROUP BY B.EMP_ML_ID, A.PERIOD, B.FC_NO, B.EMP_SS_NO, GL_ACCT_CD,
> C.SUM_SORT
Alias the column.

Daniel Morgan Received on Fri Nov 22 2002 - 15:11:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US