WITH A AS
(
SELECT 'A' DIV,   'NR1' SEQ1,   'NR1' SEQ2,   'NR1' SEQ3 FROM DUAL UNION ALL
SELECT 'A',   'SP1',   'SP1',   '' FROM DUAL UNION ALL
SELECT 'A',   'NR3',   'NR3',   '' FROM DUAL
)
, B AS
(
SELECT 'A' DIV, 'NR1' CD, 5 FREQ FROM DUAL UNION ALL
SELECT 'A', 'NR3', 4 FROM DUAL UNION ALL
SELECT 'A', 'SP1', 2 FROM DUAL
)
SELECT SEQ1
, DECODE ( SEQ1
, 'SP1'
, 1
, REGEXP_COUNT ( SEQ1||SEQ2||SEQ3, SEQ1 )
  )
* FREQ
  FROM A
, B
 WHERE a.div = b.div
   AND seq1  = b.cd

답변 감사 합니다.
제가 설명이 약간 부족했던것 같습니다.
Decode로 Seq1을 고정하신 부분인데요...
Seq별로 코드가 동일하게만 들어가지는 않습니다.

예를들어 아래와 같은 경우
Seq1 Seq2 Seq3
=============
NR1  SP1  SP1

NR1 하나와 SP1 하나로 인식해야 하는것인데
알려주신 쿼리는 Seq1을 고정해서 Count 하신것 같습니다.
제생각에는 바로 Join하지 않고 A테이블에 대하여 1차 가공이 필요해 보입니다만...

A   SP1   SP1
A   SP1   SP1   SP1
이 경우는 각 레코드당 SP1 하나씩 총 2 건?
아니면 A 하나에 SP1 은 무조건 하나로해서 총  1 건?

 
SELECT A.DIV
   , B.CD
   , REGEXP_COUNT(REGEXP_REPLACE(SEQ, 'SP1', '', INSTR(SEQ, 'SP1') + 3), B.CD) * B.FREQ
 FROM (SELECT div, LISTAGG(SEQ1 || ',' || SEQ2 || ',' || SEQ3, '^') WITHIN GROUP(ORDER BY ROWNUM) seq FROM a GROUP BY div) a
   , b
WHERE A.DIV = B.DIV

sample상 A로 했는데 Div이 각각 다르다면 ?
두개 테이블의 샘플 데이터와 해당 데이터로부터 추출되는 결과 데이터를 보여주세요.
원본예제는 있으나 실제 자료와 다르고,
결과 설명은 있긴 하지만 모호하며, 결과예제는 없네요.
명확한 예제 부탁드려요.

마농님, 정리한 내용 다시 올려드립니다.

A, B 두개의 Class에 제공되는 끼니는 각각 3끼와 2끼입니다.

class	portion_cnt
A	3
B	2

각각 Order 내역은 아래와 같은데 A class는 3끼에 대해서 Meal 1~ Meal3의 메뉴 주문을 받았고
B class는 2끼에 대해서 Meal2까지 받았습니다.
Total Portion 계산은 각 class별 끼니* 주문수량인데요
다만, SP1과 SP2는 Meal1~Meal3 어디에서 발생하든
한 끼니로만 계산하는것 입니다.
즉, Decode로 특정 컬럼을 지정하기전에 SP1, SP2는 Meal1~ Meal3상에 어디서 발생되든
하나로 인식하는 부분이 요점 같습니다.
  

class	seq	order_cnt	Meal1	Meal2	Meal3
A	1	2	MN1	MN1	MN1
A	2	1	MN2	MN3	MN2
B	3	1	MN4	MN4
B	4	1	MN5	MN5
B	5	1	MN6	MN6
B	6	5	SP1	SP2

결과는 A 9 + B 11 = 총 20이 나와야 합니다.
아래는 주문 Seq 6번의 SP1, SP2(오입력) 의 5개 주문이 2끼로 계산되어 10이되는데 이부분이
1끼로 계산되어야 하며 B class의 다른 주문들은 2끼로 계산 되어야 합니다.  

-- with 참조 SQL
WITH t1 AS
(
SELECT 'A' CLASS, 3 portion_cnt FROM DUAL UNION ALL
SELECT 'B', 2 FROM DUAL
)
, t2 AS
(
SELECT 'A' CLASS, 1 seq, 2 order_cnt, 'MN1' Meal1, 'MN1' Meal2, 'MN1' Meal3 FROM DUAL UNION ALL
SELECT 'A', 2, 1, 'MN2', 'MN3', 'MN2' FROM DUAL UNION ALL
SELECT 'B', 3, 1, 'MN4', 'MN4', '' FROM DUAL UNION ALL
SELECT 'B', 4, 1, 'MN5', 'MN5', '' FROM DUAL UNION ALL
SELECT 'B', 5, 1, 'MN6', 'MN6', '' FROM DUAL UNION ALL
SELECT 'B', 6, 5, 'SP1', 'SP2', '' FROM DUAL
)
SELECT t1.class, sum(t1.portion_cnt * t2.order_cnt) tot
FROM t1, t2
WHERE t1.class = t2.class
GROUP BY t1.class

WITH t2 AS
(
SELECT 'A' class, 1 seq, 2 order_cnt, 'MN1' Meal1, 'MN1' Meal2, 'MN1' Meal3 FROM dual
UNION ALL SELECT 'A', 2, 1, 'MN2', 'MN3', 'MN2' FROM dual
UNION ALL SELECT 'B', 3, 1, 'MN4', 'MN4', '' FROM dual
UNION ALL SELECT 'B', 4, 1, 'MN5', 'MN5', '' FROM dual
UNION ALL SELECT 'B', 5, 1, 'MN6', 'MN6', '' FROM dual
UNION ALL SELECT 'B', 6, 5, 'SP1', 'SP2', '' FROM dual
)
-- t2 만 이용해 풀었습니다.
-- 모든 메뉴가 'SP', 'MN' 으로 시작한다는 가정으로 풀었습니다.
-- 'MN' 외에도 다른 메뉴가 있다면 mn 을 주석부분으로 대체하세요
SELECT SUM(order_cnt * (sp + mn)) cnt
  FROM (SELECT class
             , seq, order_cnt
             , SIGN(INSTR(Meal1||Meal2||Meal3, 'SP')) sp
             , REGEXP_COUNT(Meal1||Meal2||Meal3, 'MN') mn
             --, CASE WHEN Meal1 NOT LIKE 'SP%' THEN 1 ELSE 0 END
             --+ CASE WHEN Meal2 NOT LIKE 'SP%' THEN 1 ELSE 0 END
             --+ CASE WHEN Meal3 NOT LIKE 'SP%' THEN 1 ELSE 0 END mn
          FROM t2
        )
;

도움 주신분들께 감사드립니다.
마농님, 추가 질문 한가지만 더 드릴게요

SIGN(INSTR(Meal1||Meal2||Meal3, 'SP')) sp
이부분인데요, SP중에서도 SP1과 SP2에 대해서만일때 (SP3는 아니라고한다면)
이런식의 체크가 되면 될까요?

SIGN(INSTR(Meal1||Meal2||Meal3, 'SP1')+ 
INSTR(Meal1||Meal2||Meal3, 'SP2')) sp

INSTR의 경우는 어떤 한가지 패턴을 찾는 함수로 보이는데
몇개 코드값이 있는지를 찾으려면 아래형태로밖에는 안되나요?

, SIGN(REGEXP_COUNT(Meal1||Meal2||Meal3, '^(SP1|SP2)$')) sp

실수했네요...SP1 과 SP2 만 체크한다면 mn 계산부분도 함께 수정되어야 할듯 합니다.
, SIGN(REGEXP_COUNT(','||Meal1||','||Meal2||','||Meal3||',', ',(SP1|SP2),')) sp
, CASE WHEN Meal1 NOT IN ('SP1', 'SP2') THEN 1 ELSE 0 END
+ CASE WHEN Meal2 NOT IN ('SP1', 'SP2') THEN 1 ELSE 0 END
+ CASE WHEN Meal3 NOT IN ('SP1', 'SP2') THEN 1 ELSE 0 END mn
SP1, SP2 외에 SP로 시작하는게 뭐가 있는지 몰라 ',' 를 붙여 비교했습니다.
SP12 나 SP21 등을 제외하기 위함이구요. 숫자가 두자리 이상 안온다면
컴마를 붙이는 수고는 안해도 됩니다.