Hi Don, As Michael demonstrated, encapsulating the original query as a subquery, you can leave the working part alone. The outer query simply pulls the results then transforms the data (in this case the count) into the format you are looking for. You don't need to count twice since the same count value is usable for your third column.
In case you were wondering why your original method didn't work, http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions032.htm Oracle's specification for the COUNT function in an aggregation does not allow anything outside of the call "COUNT( )" except perhaps in the case of an analytical function... as in: count() over ... Anyways, here was more of an explanation than you probably care to read about when the solution was already posted. Sometimes as a developer, I like to also know the "why" behind things. Rich On Wed, May 19, 2010 at 9:38 AM, Michael Moore <[email protected]>wrote: > try > SELECT sic_desc, ai_doc_num_cnt, ai_doc_num_cnt * 2000 times_2000 > FROM ( SELECT mine_tbl.sic_desc sic_desc, COUNT( * ) ai_doc_num_cnt > FROM inqprod.acc_inj_tbl, inqmsis.mine_tbl > WHERE ( ( inqmsis.mine_tbl.c_m_ind = 'M' ) > AND( inqprod.acc_inj_tbl.inj_degr_cd IN( '01', '02', > '03', '04', '05', '06' )) > AND( ( TO_NUMBER( TO_CHAR( inqprod.acc_inj_tbl.ai_dt, > 'YYYY' ))) = 2009 ) > ) > AND inqprod.acc_inj_tbl.mine_id = inqmsis.mine_tbl.mine_id > GROUP BY inqmsis.mine_tbl.sic_desc > ORDER BY 1 ); > > On Wed, May 19, 2010 at 8:27 AM, Don B <[email protected]> wrote: > >> select INQMSIS.mine_tbl.sic_desc, COUNT >> (INQPROD.ACC_INJ_TBL.AI_DOC_NUM), COUNT >> (INQPROD.ACC_INJ_TBL.AI_DOC_NUM) * 200000 from INQPROD.ACC_INJ_TBL, >> INQMSIS.mine_tbl where ((INQMSIS.mine_tbl.c_m_ind = 'M') and >> (INQPROD.ACC_INJ_TBL.INJ_DEGR_CD IN ('01', '02', '03', '04', '05', >> '06')) and ((to_number(to_char(INQPROD.ACC_INJ_TBL.AI_DT,'YYYY' ))) = >> 2009)) and INQPROD.ACC_INJ_TBL.MINE_ID = INQMSIS.mine_tbl.mine_id >> group by INQMSIS.mine_tbl.sic_desc order by 1 ; >> >> I am attempting to modify the above query but everytime I insert the >> second count statement times 200000, the query will not perform the >> calculation. What I'm trying to do is show another column "count >> document number" but this is times 200000. What am I doing wrong? >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
