Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: sql group by and sub-select problem
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