SIMPLE =(+) 1

[Guest_imported]

Hello,

I have queried statuses from dual:

[tt]
select 'I' AS BLA FROM DUAL
union
select 'A' AS BLA FROM DUAL
union
select 'W' AS BLA FROM DUAL
union
select 'V' AS BLA FROM DUAL
union
select 'E' AS BLA FROM DUAL
union
select 'P' AS BLA FROM DUAL
union
select 'N' AS BLA FROM DUAL
[/tt]

I would now like to make a query on another table to count how many times each of these statuses exists in that table. When does not exist at all, 0 should be displayed. What do I put in place of ??? in the following query?

The =(+) syntax is difficult for me.

[tt]
select
trn_stat_typ,
NVL(count(trn_stat_typ),0)
from
vegas.atoim
PROCESSDT >= to_date('01.01.2000','dd.MM.yyyy')
and PROCESSDT <= to_date('01.01.2004','dd.MM.yyyy')
and TRN_STAT_TYP =(+) (???)
GROUP BY
trn_stat_typ
[/tt]

Would be thankful for all help getting me closer for the solution. Thank you!

Sincerely,
Aulo

 

[angiole]

I am assuming the Oracle is being used here with the notation I see.

You may try the following:

select stat
,stat_id
,tcount
from
(
select 0 stat from dual union all
select 1 stat from dual union all
select 2 stat from dual union all
select 3 stat from dual union all
select 4 stat from dual union all
select 5 stat from dual ) all_stats,
(
select stat_id
,count(*) tcount
from r_12
where rownum < 5000
group by
stat_id ) all_trans
where all_stats.stat = all_trans.stat_id(+)
order by 1
/


The first inner query collects alues you wish to see.
The second collects the actuals.
The outer query will join the two to produce your report, as follows:

STAT TSTAT_ID TCOUNT
---------- ---------- ----------
0
1 1 534
2 2 4465
3
4
5


Enjoy

 

[crufty]

Nice solution, angiole. The select needs a slight edit in order to give the output in the format that they wante:

select stat, NVL( tcount, 0 )
.
.
.

(NOTE: NVL is an Oracle specific function)

This will give (using your example numbers from your post)

STAT TCOUNT
---------- ----------
0 0
1 534
2 4465
3 0
4 0
5 0

 

[angiole]

Wunderbar
AA