sql - Counting occurrences of an id in a database table -

i'm having trouble oracle database query , related subqueries. @ it's core, problem count number of times id 1 table occurs in table.

the problem: have 2 tables, orders table, stores information on ordered items web service. data table run through process (which have no control over) , result placed fulfilled table.

order numbers not unique 1 item. each order can have large number of items, , each item stored on line. items, however, can combo/package , process handles. item, game_pack example, can come orders table , out on other end comes out game1, game2, game3 , associated order number.

the problem is, these items don't come out of process correctly , line_item may not associated fulfilled item. way can, resources available, determine if there issue getting maximum line_number , comparing number of fulfilled_item groups.

what i've tried: @ first thought simple do, using rownumber() or denserank() analytical function on partition order number, has become more confusing that. query working with:

select *  from(     select max (item_index) on (partition tbl.item_number) item_count, tbl.*         (             select i.item_fulfill_number, i.order_number, row_number()over(partition i.item_number, i.order_number order i.order_number) item_index              fulfilled_items ) tbl             ) results              inner join (                 select *                  (                     select orderinfo.order_number order_order_number, orderinfo.line_number, orderinfo.ordered_item, row_number() over(partition orderinfo.order_number order orderinfo.line_number desc) order_row                      orderinfo                     )                  order_row <= 1                 )             on results.order_number = order_order_number results.item_count = results.item_index , ordered_item 'game%' 

note right pulling when counts match, logic reversed when query works

constraints

• i not have access process splits items
• the query should run quickly, working upwards of 50,000 possible records
• the query tested @ 22 seconds on 2 minutes execution time
• pagination going used, if answer, don't worry including it, consider because can or hurt speed of query
• i cannot touch table structure

table structure , graphic representation (maximum line number represents number of fulfilled_item groups)

thank taking time read this.

edit results should this:

where item comes orders table, , result ok, bad based on whether or not fulfilled groups matches max line number.

if i'm understanding correctly, each order should have same number of fulfillment groups there line numbers in order. each fulfillment group of unknown size , represented unique fulfillment number. based on that, think query should simple this:

select    main.*,    'bad' result (     select distinct       o.order_number,       count(o.line_number) on (partition o.order_number) order_lines,       (select count(distinct item_fulfill_number) fulfilled_items f f.order_number = o.order_number) fulfilled_groups     orders o ) main order_lines != fulfilled_groups 

the subquery counts number of lines (just in case line number gets skipped, change max on line number if want to) , number of distinct fulfillment groups. overall query returns orders 2 counts not equal.