Richard
Thanks for the quick response. I appreciate your help. Don From: oracle-plsql@googlegroups.com [mailto:oracle-pl...@googlegroups.com] On Behalf Of Richard Pascual Sent: Wednesday, May 19, 2010 12:58 PM To: oracle-plsql@googlegroups.com Subject: Re: Developing a query 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/functions 032.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 <michaeljmo...@gmail.com> 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 <braenovich....@dol.gov> 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
