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%'
