Prev: COUNT Function
Next: Oracle Data-Files Binary Format
From: Don B on 19 May 2010 11:50
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 trying to modify a query to show an additional column. This
column is suppose to multiply the Count Document No. column by
200,000. I am a beginner and cannot figure out what I'm doing wrong.
Can somebody get me on the right path.

Thanks
Don
From: Mark D Powell on 21 May 2010 10:13
On May 19, 11:50 am, Don B <Braenovich....(a)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 trying to modify a query to show an additional column.  This
> column is suppose to multiply the Count Document No. column by
> 200,000.  I am a beginner and cannot figure out what I'm doing wrong.
> Can somebody get me on the right path.
>
> Thanks
> Don

It would be helpful if you would post the actual Oracle error message
you are receiving or at least explicitly state what issue you face.

Generally speaking when you use a group by clause every column in the
select list either has to be part of the group by clause or have an
aggregate function applied to it. If you try to reference individual
column values you get an error.

Perhaps you want sum(col * 200000) or 200000 * sum(col) but from your
post I cannot tell.

HTH -- Mark D Powell --


From: Shakespeare on 21 May 2010 11:02
Op 21-5-2010 16:13, Mark D Powell schreef:
> On May 19, 11:50 am, Don B<Braenovich....(a)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 trying to modify a query to show an additional column. This
>> column is suppose to multiply the Count Document No. column by
>> 200,000. I am a beginner and cannot figure out what I'm doing wrong.
>> Can somebody get me on the right path.
>>
>> Thanks
>> Don
>
> It would be helpful if you would post the actual Oracle error message
> you are receiving or at least explicitly state what issue you face.
>
> Generally speaking when you use a group by clause every column in the
> select list either has to be part of the group by clause or have an
> aggregate function applied to it. If you try to reference individual
> column values you get an error.
>
> Perhaps you want sum(col * 200000) or 200000 * sum(col) but from your
> post I cannot tell.
>

What is the difference?

> HTH -- Mark D Powell --
>
>

Shakespeare

From: Shakespeare on 21 May 2010 11:06
Op 19-5-2010 17:50, Don B schreef:
> 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 ;
>

Try something like this:




select sic_desc, mycount, 200000 * mycount myproduct from (
select INQMSIS.mine_tbl.sic_desc, COUNT
(INQPROD.ACC_INJ_TBL.AI_DOC_NUM) mycount 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 ;


Shakespeare

From: joel garry on 21 May 2010 12:11
On May 21, 8:02 am, Shakespeare <what...(a)xs4all.nl> wrote:
> Op 21-5-2010 16:13, Mark D Powell schreef:
>
>
>
> > On May 19, 11:50 am, Don B<Braenovich....(a)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 trying to modify a query to show an additional column.  This
> >> column is suppose to multiply the Count Document No. column by
> >> 200,000.  I am a beginner and cannot figure out what I'm doing wrong..
> >> Can somebody get me on the right path.
>
> >> Thanks
> >> Don
>
> > It would be helpful if you would post the actual Oracle error message
> > you are receiving or at least explicitly state what issue you face.
>
> > Generally speaking when you use a group by clause every column in the
> > select list either has to be part of the group by clause or have an
> > aggregate function applied to it.  If you try to reference individual
> > column values you get an error.
>
> > Perhaps you want sum(col * 200000) or 200000 * sum(col) but from your
> > post I cannot tell.
>
> What is the difference?
>
> > HTH -- Mark D Powell --
>
> Shakespeare

It is possible to have rounding issues do funny things in sqlplus
formatting. It may also be possible to have rounding differences in
this type of sum and multiplication, depending on col values. Not
everything can go cleanly from decimal to binary, even with 38 digits
of precision.

jg
--
@home.com is bogus.
http://yro.slashdot.org/story/10/05/21/1127210/Facebook-Others-Giving-User-Private-Data-To-Advertisers
 | 
Pages: 1
Prev: COUNT Function
Next: Oracle Data-Files Binary Format