December 29, 2010 at 8:27 am
Hi,
Can some one help in solving this...... hav a query and output as
select
input_file,
rec_cnt,
count(*) load_cnt,
(rec_cnt-load_cnt) reject_cnt
from month_in_temp
group by 1,2 order by 1;
output:
--------
input_file rec_cnt load_cnt reject_cnt
------------------- --------- -----------
MONTH_1.TXT 261229 261229 0
MONTH_2.TXT 261681 261681 0
MONTH_3.txt 221935 221935 0
MONTH_4.txt 270668 270668 0
MONTH_5.TXT 231666 80370 151296
MONTH_6.txt 148023 148023 0
MONTH_7.TXT 38399 38399 0
How we calculate the SUM of (reject_cnt) from the output of the query.... it amy be another query....can some one help mee..
thank u
December 29, 2010 at 8:36 am
It looks like it would be:
select sum(rec_cnt)-count(*) as total_reject_cnt
Without the Group By, that should get you what you need, I think.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 8:47 am
Hi,
If we perform SUM(rec_cnt)
it shows an error saying
Numeric overflow occurred during computation.
December 29, 2010 at 9:02 am
you may need to convert to a bigint or some other larger data type
sum(convert(bigint,rec_cnt))
Jim
December 29, 2010 at 9:31 am
Thnk U bro
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply