oracle11g - How to merge a subquery result to the results of another subquery per row?

Question

I'm struggling to find a way to get my desired output. It's the subquery that making it hard for me. Need some help pls.

TABLE: Sales
DATE        INVOICE#   AMT      TYPE       
01-DEC-20   10001      50.00    TAKEAWAY   
01-DEC-20   10002      50.00    DELIVERY   
01-DEC-20   10003      50.00    DINEIN     
01-DEC-20   10004      50.00    TAKEAWAY   
01-DEC-20   10005      50.00    DELIVERY   
01-DEC-20   10006      50.00    DINEIN     
02-DEC-20   10001      100.00   TAKEAWAY   
02-DEC-20   10002      100.00   DELIVERY   
02-DEC-20   10003      100.00   DINEIN     
02-DEC-20   10004      100.00   TAKEAWAY   
02-DEC-20   10005      100.00   DELIVERY   
02-DEC-20   10006      100.00   DINEIN    

Below is my desired output report.

DATE        TOTAL_SALES    TYPE       SUBTOTAL_TYPE   PERC(SUBTOTAL / TOTAL)
01-DEC-20   300            TAKEAWAY   100             33.3%
01-DEC-20   300            DELIVERY   100             33.3%
01-DEC-20   300            DINEINE    100             33.3%
02-DEC-20   600            TAKEAWAY   200             33.3%
02-DEC-20   600            DELIVERY   200             33.3%
02-DEC-20   600            DINEIN     200             33.3%

So basically, what I'm struggling with is the TOTAL_SALES column.

Answer 1

This works for me - I tested on an 18.1 db but this functionality is already in 11g I believe

WITH test_data(dt, invoice, amt, type)
AS
(
SELECT '01-DEC-20',   '10001'      ,50    ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10002'      ,50    ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10003'      ,50    ,'DINEIN' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10004'      ,50    ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10005'      ,50    ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '01-DEC-20',   '10006'      ,50    ,'DINEIN' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10001'      ,100   ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10002'      ,100   ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10003'      ,100   ,'DINEIN' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10004'      ,100   ,'TAKEAWAY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10005'      ,100   ,'DELIVERY' FROM DUAL  UNION ALL
SELECT '02-DEC-20',   '10006'      ,100   ,'DINEIN'  FROM DUAL
)
SELECT 
 distinct dt,
SUM(AMT) OVER (PARTITION BY dt) as total_sales,
type,
SUM(AMT) OVER (PARTITION BY dt,type) as subtotal_type,
to_char(SUM(AMT) OVER (PARTITION BY dt,type)/SUM(AMT) OVER (PARTITION BY dt)*100,'990.00')||'%'
FROM test_data
ORDER BY dt;