PL SQL Query Problems

[campbere]

I am trying to figure out an efficient query to solve the folloing problem.

I have a table that has approx 80,000 rows. I need to select all the prod_ids that have prod_flag = 'OH'. I then need to break it down to find out how many of the prod_ids have only been produced in OH, and finally I need a break out of the prod_ids that were produced in OH and KY.

so if I have

prod id item prod_flag
1A widget OH
1A x widget KY
2B z widget OH
2B widget master OH
2B widget junior OH
3C miss widget OH
3C miss jr OH
4D widget sr KY
4D widget II OH

So looking at this I want to get these numbers
Total prod Id produced in Ohio 4
Total prod Id produced only in Ohio 2
Total prod Id in ohio or other 2

Does this make sense?

I have been working on some queries to handle this but they are too slow and have been incorrect. I welcome any suggestions.

For the Total prod Id produced in Ohio I had

select count(distinct prod_id)
from table
where prod_flag = 'OH'

For the Total prod Id produced only in Ohio I had

select prod_id
from table
where prod_flag = 'OH'
minus
select prod_id
from table
where prod_flag != 'OH'

The third value I haven't been able to figure out. I welcome any comments or suggestions you may have.

Thanks

 

[MikeJones]

Try....

select 'Total prod Id produced in Ohio '|| count(distinct prod_id) string_data
from table
where prod_flag = 'OH'
union
select 'Total prod Id produced only in Ohio '|| count(distinct prod_id) string_data
from table t1
where prod_flag = 'OH'
and not exists (select 1
from table t2
where t1.prod_id = t2.prod_id
and t2.prod_flag != 'OH')
union
select 'Total prod Id produced in Ohio or other'|| count(distinct prod_id) string_data
from table t1
where prod_flag = 'OH'
and exists (select 1
from table t2
where t1.prod_id = t2.prod_id
and t2.prod_flag != 'OH')
and not exists (select 1
from table t2
where t1.prod_id = t2.prod_id
and t2.prod_flag != 'OH')

Make sure you have an index on prod_id even if it is non unique. Or if there are not many distinct prod_id's in the table you may want to consider a bitmap index.

HTH,

Mike.