Group By syntax while using Cross Join

Question

i wish to apply a Group By condition to my SQL below so that the O/P will display POL# with GP.

select t.POl#, (DEB.CUSTD - CRED.CUSTC) AS GP 
from (
        (
            select POL.SP_NUM POL#
            ,      sum(D.AMT) AS CUSTD 
            from   S_INVOICE D
            ,      S_ASSET POL 
            where  POL.ROW_ID           =   D.FN_ACCNT_ID 
            and    POL.SP_NUM           in  ('000','111','222') 
            and    D.DEBIT_TYPE         =   'Customer' 
            group by POL.SP_NUM
        ) DEB
        CROSS JOIN
        (
            select pol.SP_NUM POL#
            ,      sum(C.AMT) AS CUSTC 
            from   S_SRC_PAYMENT C
            ,      S_ASSET POL
            where  POL.ROW_ID           =   C.ASSET_ID 
            and    POL.SP_NUM           in  ('000','111','222') 
            and    C.CG_DEDN_TYPE_CD    =   'Customer' 
            group by POL.SP_NUM
        ) CRED
    ) t
    group by t.POL#

When i execute the same I am getting "ORA-00933: SQL command not properly ended" error, where the cursor points to 't'

Kindly assist.

Expected O/P:
POL#    GP
000     800
111     120
222     50

Appending Sample Data with explanation for better understanding of requirement:

Table 1:

S_ASSET
ROW_ID    POL#
1         000
2         111
3         222
4         333
5         444

Table 2:

S_INVOICE (Debit Table)
FN_ACCNT_ID    POL#    DEBIT_TYPE          AMT
1              000     Customer            10
1              000     Customer            10
1              000     Insurer             5
2              111     Customer            10
3              222     Customer            10
3              222     Insurer             5
5              444     Insurer             10

Table 3:

S_SRC_PAYMENT (Credit Table)
ASSET_ID    POL#    CG_DEDN_TYPE_CD     AMT
1           000     Insurer             10
1           000     Insurer             10
1           000     Customer            5
2           111     Insurer             10
3           222     Insurer             5
3           222     Insurer             5
3           222     Customer            5
5           444     Customer            10

As per this query I will consider "Customer" records per POL# and SUM the AMT. (Every Debit for the Customer will Credit Insurer(s) per POL# & vice-versa)

Expected O/P (Sum of Debits - Sum of Credits) for a Customer per POL#

POL #       AMT (GP)
000         15
111         10
222         5
333         0
444        -10

Answer 1

You obviously only want to get the deb and cred per s_asset and then aggregate so as to get the sums. You can do this without a join and subquery the sums directly instead:

select 
  sp_num as pol#,
  sum(<get deb sum for the pol.row_id here>) - sum(<get cred sum for the pol.row_id here>)
from s_asset pol 
where sp_num in ('000','111','222')
group by sp_num;

The complete query:

select 
  sp_num as pol#,
  coalesce(sum(
    (
      select sum(deb.amt)
      from s_invoice deb
      where deb.fn_accnt_id = pol.row_id
      and deb.debit_type = 'Customer' 
    )
  ), 0) -
  coalesce(sum(
    (
      select sum(cred.amt) 
      from s_src_payment cred
      where cred.asset_id = pol.row_id
      and cred.cg_dedn_type_cd = 'Customer' 
    ), 0)
  ) as gp
from s_asset pol 
where sp_num in ('000','111','222')
group by sp_num;

The same with joins instead:

select 
  pol.sp_num as pol#,
  coalesce(sum(deb.total), 0) - coalesce(sum(cred.total), 0) as gp
from s_asset pol
left join
(
  select fn_accnt_id as pol_row_id, sum(deb.amt) as total
  from s_invoice
  where  debit_type = 'Customer' 
  group by fn_accnt_id 
) deb on deb.pol_row_id = pol.row_id
left join
(
  select asset_id as pol_row_id, sum(amt) as total
  from s_src_payment
  where  cg_dedn_type_cd = 'Customer' 
  group by asset_id
) cred on cred.pol_row_id = pol.row_id
group by pol.sp_num;