need select results in same return of data

[joehnk]

Having brain freeze and could use some help with this SQL...

I want to select the good and bad records from the same table and return the results in the same statement:

What I have tried to this point does not work but looks something like:

select count(good), count(bad) from (

select reject_code good from final_collection where date_made > sysdate - 12/24
and reject_code = 'GOOD'

union all

select reject_code bad from final_collection where date_made > sysdate - 12/24
and reject_code <> 'GOOD'

)


I figure I am missing something simple, but no luck so far.

When I understand this better I plan to add the same type query for good/bad from additional tables.

Any assistance is greatly appreciated.

John Joehnk

 

[SantaMufasa]

John,

Please post the evidence that you are having "no luck so far": Does this mean you are having a syntax error, a run-time error, or a logic-error (unexpected/wrong results)? A cursory look at your code appears that your code is okay.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

If your final_collection table is quite big then your approach is not quite efficient as you scan your table twice.

This one seems to be a better one:

select count(decode(reject_code,'GOOD',1)),
   count(decode(reject_code,'GOOD',null,1))
from final_collection

Regards, Dima

http://seminihin.narod.ru

 

[stefanhei]

@John
Dima already solved your problem in his post. I'll try to help you to unfreeze your brain:
Your UNION ALL - query returns only one column containing all reject codes of the last 12 hours and nothing else - no way to differentiate good and bad.
As you name the column "good" in the first part of your inline view that's its name. That is why you'll get an error message saying something like "invalid column name".

Stefan

 

[Beilstwh]

Another way would be

select reject_code ,count(*)
from final_collection
where date_made > sysdate - 12/24
group by reject_code;

Bill
Oracle DBA/Developer
New York State, USA

 

[sem]

Sure, Bill, but only when final_collection contains only good and bad records. As you may see from the original example everything not good is supposed to be bad, but that doesn't mean that it is indeed, as something may be just not good enough

Regards, Dima

http://seminihin.narod.ru