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).

SELECT
  t.reporting_date "Reporting Date",
  t.district_code "BU Code",
  t.district_desc "BU Desc",
  t.contract "Site",
  to_char( SUM(t.Daily_Order_Entry), '99,999,999.99') "Daily Order Entry",
  to_char( SUM(t.MTD_Order_Entry), '99,999,999.99') "MTD Order Entry",
  to_char( SUM(t.Daily_Billings), '99,999,999.99') "Daily Billings",
  to_char( SUM(t.MTD_Billings), '99,999,999.99') "MTD Billings",
  to_char( MAX(btl.amount), '99,999,999.99') "Forecast",
  to_char( SUM(t.Past_Open_Orders), '99,999,999.99') "Past Open Orders",
  to_char( SUM(t.Curr_Mth_Not_Held), '99,999,999.99') "Curr Mth Not Held",
  to_char( SUM(t.Curr_Mth_Held), '99,999,999.99') "Curr Mth Held",
  to_char( SUM(t.Curr_Mth_Subtotal), '99,999,999.99') "Curr Mth Subtotal",
  to_char( SUM(t.Future_Held), '99,999,999.99') "Future Held",
  to_char( SUM(t.Future_Not_Held), '99,999,999.99') "Future Not Held",
  to_char( SUM(t.Total_Open), '99,999,999.99') "Total Open"

FROM REGA1APP.RW_DAILY_SALES_AGG t

left join BUDGET_TEMPLATE_ROW btl
on 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

WHERE t.reporting_date = NVL(to_date('&Report_Date','MM/DD/YYYY'),TRUNC(SYSDATE-1))

  AND t.district_code LIKE '6%'

  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)

GROUP BY
  t.reporting_date,
  t.district_code,
  t.district_desc,
  t.contract

UNION ALL

SELECT
  NULL,
  'Total',
  '',
  '',
  to_char( SUM(t.Daily_Order_Entry) , '99,999,999.99'),
  to_char( SUM(t.MTD_Order_Entry) , '99,999,999.99'),
  to_char( SUM(t.Daily_Billings) , '99,999,999.99'),
  to_char( SUM(t.MTD_Billings) , '99,999,999.99'),
  to_char( SUM(btl.amount) , '999,999,999,999.99'),
  to_char( SUM(t.Past_Open_Orders) , '99,999,999.99'),
  to_char( SUM(t.Curr_Mth_Not_Held) , '99,999,999.99'),
  to_char( SUM(t.Curr_Mth_Held) , '99,999,999.99'),
  to_char( SUM(t.Curr_Mth_Subtotal) , '99,999,999.99'),
  to_char( SUM(t.Future_Held) , '99,999,999.99'),
  to_char( SUM(t.Future_Not_Held) , '99,999,999.99'),
  to_char( SUM(t.Total_Open) , '99,999,999.99')

FROM REGA1APP.RW_DAILY_SALES_AGG t

left join BUDGET_TEMPLATE_ROW btl
on 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%'

WHERE reporting_date = NVL(to_date('&Report_Date','MM/DD/YYYY'),TRUNC(SYSDATE-1))

  AND t.district_code LIKE '6%'