Re: Cartesian product with totals

In article <86g57.22853$B5.1729039_at_monolith.news.easynet.net>, "Noah" says...
>
>> it looks like you want to get the count of dir_reject_id for
 dih_cb_trans_batch
>> 4942 VERSUS the count for all, and the sum for 4942 VERSUS the sum for
 all -- if
>> so, a simple decode and a two table join is all you need. We use decode
 to
>> either contribute the dir_reject_id or NULL to the count (count only
 counts
>> non-null entries) and again to contribute either dir_amount or ZERO to the
 sum
>>
>> If that is the query you were going for (your query wouldn't get that as
 you
>> join DIH to DIR and the save only the records from DIR such that the batch
 =
>> 4942 and then join that to DIR2 -- meaning only batch 4942 would be in
 that
>> result as well, just multiplied), this might work:
>
>Thanks for the suggestion.
>
>What I want is a count/total of those with a dir_rejection_reason of 'No CC
>line
>produced' and a count/total of those which haven't.
>
>Does that make sense?

take a look at the decode I provided you. It shows how to get a count of something when some field = some value vs when it doesn't.

Reading your original query, I made some assumptions -- that it was keyed off of batch=4929, just use the rejection reason instead of batch in the decode to get what you want.

>--
>Noah
>Remove ".spam.begone"
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp