Solved

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

  • 23 September 2020
  • 6 replies
  • 2826 views

Userlevel 4
Badge +10
  • Sidekick (Customer)
  • 119 replies

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

 

icon

Best answer by paul harland 24 September 2020, 02:21

View original

6 replies

Userlevel 7
Badge +24

If it was me I would add some brackets

 

select * from (

(select * from A join B)

union all 

(select * from C)

)

Userlevel 4
Badge +10

Thx!

Badge +1

Have you looked at using the OUTER APPLY option in Oracle? This might let you achieve what you are trying to do and would simplify this.

Userlevel 4
Badge +10

thx for the idea; I will try that...

Badge +3

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

 

 Could someone share the modified ones (the fulkl query statement) by adding more braces.

Badge +3

@Yasas Kasthuriarachchi 

Reply