Quick Report: UNION SQL: Total SELECT join SUM() Not Working

I have a Quick Report that Groups By dollar columns, then does a UNION to total those $$$ columns. It has a join column from another table (btl.amount), keying on primary keys/columns.

The challenge I’m running into is that, on the UNION SQL, I’m joining to a table to get that single $$$ column’s total (SUM(btl.amount)). Since the join is not at the detail level of each main table’s rows, it’s overstating the SUM() total $$$. (kind of hard to explain).

1SELECT
2  t.reporting_date "Reporting Date",
3  t.district_code "BU Code",
4  t.district_desc "BU Desc",
5  t.contract "Site",
6  to_char( SUM(t.Daily_Order_Entry), '99,999,999.99') "Daily Order Entry",
7  to_char( SUM(t.MTD_Order_Entry), '99,999,999.99') "MTD Order Entry",
8  to_char( SUM(t.Daily_Billings), '99,999,999.99') "Daily Billings",
9  to_char( SUM(t.MTD_Billings), '99,999,999.99') "MTD Billings",
10  to_char( MAX(btl.amount), '99,999,999.99') "Forecast",
11  to_char( SUM(t.Past_Open_Orders), '99,999,999.99') "Past Open Orders",
12  to_char( SUM(t.Curr_Mth_Not_Held), '99,999,999.99') "Curr Mth Not Held",
13  to_char( SUM(t.Curr_Mth_Held), '99,999,999.99') "Curr Mth Held",
14  to_char( SUM(t.Curr_Mth_Subtotal), '99,999,999.99') "Curr Mth Subtotal",
15  to_char( SUM(t.Future_Held), '99,999,999.99') "Future Held",
16  to_char( SUM(t.Future_Not_Held), '99,999,999.99') "Future Not Held",
17  to_char( SUM(t.Total_Open), '99,999,999.99') "Total Open"
18
19FROM REGA1APP.RW_DAILY_SALES_AGG t
20
21left join BUDGET_TEMPLATE_ROW btl
22on btl.COMPANY = 'MASTER1' and btl.BUDGET_PROCESS_ID = 'FRCST2020' and BUDGET_TEMPLATE_ID = 'FRC-BU-SITE' and t.district_code = btl.code_b and t.contract = btl.code_c
23
24WHERE t.reporting_date = NVL(to_date('&Report_Date','MM/DD/YYYY'),TRUNC(SYSDATE-1))
25
26  AND t.district_code LIKE '6%'
27
28  AND (NVL(t.Daily_Order_Entry,0)<>0 OR NVL(t.MTD_Order_Entry,0)<>0 OR NVL(t.Daily_Billings,0)<>0 OR NVL(t.MTD_Billings,0)<>0 OR NVL(t.Total_Open,0)<>0)
29
30GROUP BY
31  t.reporting_date,
32  t.district_code,
33  t.district_desc,
34  t.contract

1UNION ALL
2
3SELECT
4  NULL,
5  'Total',
6  '',
7  '',
8  to_char( SUM(t.Daily_Order_Entry) , '99,999,999.99'),
9  to_char( SUM(t.MTD_Order_Entry) , '99,999,999.99'),
10  to_char( SUM(t.Daily_Billings) , '99,999,999.99'),
11  to_char( SUM(t.MTD_Billings) , '99,999,999.99'),
12  to_char( SUM(btl.amount) , '999,999,999,999.99'),
13  to_char( SUM(t.Past_Open_Orders) , '99,999,999.99'),
14  to_char( SUM(t.Curr_Mth_Not_Held) , '99,999,999.99'),
15  to_char( SUM(t.Curr_Mth_Held) , '99,999,999.99'),
16  to_char( SUM(t.Curr_Mth_Subtotal) , '99,999,999.99'),
17  to_char( SUM(t.Future_Held) , '99,999,999.99'),
18  to_char( SUM(t.Future_Not_Held) , '99,999,999.99'),
19  to_char( SUM(t.Total_Open) , '99,999,999.99')
20
21FROM REGA1APP.RW_DAILY_SALES_AGG t
22
23left join BUDGET_TEMPLATE_ROW btl
24on btl.COMPANY = 'MASTER1' and btl.BUDGET_PROCESS_ID = 'FRCST2020' and BUDGET_TEMPLATE_ID = 'FRC-BU-SITE'  and btl.amount <> 0 and btl.year_period = '2020-09'  and t.district_code = btl.code_b and t.contract = btl.code_c AND btl.code_b LIKE '6%'
25
26WHERE reporting_date = NVL(to_date('&Report_Date','MM/DD/YYYY'),TRUNC(SYSDATE-1))
27
28  AND t.district_code LIKE '6%'