# Thread: Performing calculations on sums unioned together

1. Registered User
Join Date
Dec 2012
Posts
5

## Unanswered: Performing calculations on sums unioned together

I have a query that works perfectly, I get the exact values I need, except I need to add another column.

The top union provides the current month and future 12 months, where the bottom union provides the past 12 months.

Here is the select statement, leaving out the tables and joins:

SELECT sales_date as Sales_Date,
SUM (proj_on_hand * xx_conso_cost) as Projected_on_hand,
SUM((xx_sales_fcst + xx_trend_fcst)*xx_consol_cost) as Cost_of_FCST
Group by sales_date

union

SELECT sales_date as Sales_Date,
(SUM(xx_prod_inv))*1000 as Projected_on_hand,
(SUM(xx_prod_cost))*1000 as Cost_of_FCST
group by sales_date

The select statement returns results like the following:
Code:
```Month    -     Projected_on_hand             -          Cost_of_FCST
Oct 12   -            10                     -             3
Nov 12   -             8                      -            5
Dec 12   -             9                     -             4
Jan 12   -             11                    -             6
etc```

I want to keep displaying the month, projected_on_hand, and cost_of_fcst, but I also want to display an additional column.

I want to the sum the previous 3 months of cost_of_fcst and divide them by the sum of the previous 3 months of projected on_hand.

So the new column for Jan 12 should be .44 because (3+5+4)/(10+8+9)
The new column for Feb 12 should be .53 because (5+4+6)/(8+9+11)

I am in an oracle database environment, can I do this using a cursor? Any idea on how I would go about that?

Or do I have to go about creating a global temporary table?

2. Registered User
Join Date
Dec 2012
Posts
5
I figured it out, now all I need to do is write a procedure to tie the steps together.

create global temporary table temp_subd_net_need
on commit preserve rows
as (select statement)

;

SELECT T1.Sales_Date,
T1.Proj_on_Hand,
T1.Cost_Of_Forecast,
round(((12 * (T2.Cost_of_forecast + T3.Cost_of_forecast + T4.Cost_of_forecast ))/(T2.Proj_on_hand + T3.Proj_on_hand + T4.Proj_on_hand)),2) as Turn_Rate

FROM temp_subd_net_need T1,
temp_subd_net_need T2,
temp_subd_net_need T3,
temp_subd_net_need T4
WHERE T1.Sales_Date = add_months (T2.Sales_date , -1)
AND T1.Sales_Date = add_months (T3.Sales_date , -2)
AND T1.Sales_Date = add_months (T4.Sales_date , -3)
Last edited by MTSkibum; 12-19-12 at 19:24.

3. Registered User
Join Date
Dec 2012
Posts
5
And just incase anyone is curious about the whole sql

CREATE global TEMPORARY TABLE temp_subd_net_need ON COMMIT preserve rows
AS
(SELECT net.sales_date Sales_Date,
DECODE(SIGN(SUM(ROUND((NVL(net.xx_net_need, 0) * NVL(net.xx_conso_cost, 0)),0))), -1,0, SUM(ROUND((NVL(net.xx_net_need, 0) * NVL(net.xx_conso_cost, 0)),2))) Net_Need,
SUM(ROUND((NVL(net.proj_on_hand, 0) * NVL(net.xx_conso_cost, 0)),0)) Proj_on_hand,
SUM(ROUND((NVL(net.safety_stk, 0) * NVL(net.xx_conso_cost, 0)),0)) Safety_stock,
SUM(ROUND(((NVL(sda.xx_sales_fcst, 0) + (NVL(sda.xx_trend_fcst, 0)) )* NVL(sda.xx_consol_cost, 0)),0)) Cost_of_Forecast,
SUM(ROUND(((NVL(sda.xx_sales_fcst, 0) + (NVL(sda.xx_trend_fcst, 0))) * NVL(sda.xx_asp, 0)),0)) Revenue_of_Forecast,
(SUM(ROUND(((NVL(sda.xx_sales_fcst, 0) + (NVL(sda.xx_trend_fcst, 0))) * NVL(sda.xx_asp, 0)),0)) - SUM(ROUND(((NVL(sda.xx_sales_fcst, 0)+ (NVL(sda.xx_trend_fcst, 0)) )* NVL(sda.xx_consol_cost, 0)),0)) ) Forecasted_Margin
FROM dm01.supply_plan_data net,
dm01.location loc,
dm01.T_EP_MIS_SUBDIS sub,
DM01.items itm,
dm01.sales_data sda
WHERE net.location_id =loc.location_id
AND loc.T_EP_MIS_SUBDIS_EP_ID = sub.T_EP_MIS_SUBDIS_EP_ID
AND net.item_id =itm.item_id
AND loc.location_id = sda.location_id
AND net.sales_date = sda.sales_date
AND itm.item_id = sda.item_id
AND sub.MIS_SUBDIS_DESC = 'Germany Sub-Dist'
GROUP BY net.sales_date
UNION
SELECT sda.sales_date Sales_Date,
0 AS Net_Need,
(SUM(ROUND(NVL(sda.xx_prod_inv, 0) ,2)))*1000 Proj_on_hand,
0 AS Safety_stock,
(SUM(ROUND(NVL(sda.xx_prod_cost, 0) ,2)))*1000 Cost_of_Forecast,
(SUM(ROUND(NVL(sda.xx_prod_rev, 0) ,2))) *1000 Revenue_of_Forecast,
((SUM(ROUND(NVL(sda.xx_prod_rev, 0) ,2)))-(SUM(ROUND(NVL(sda.xx_prod_cost, 0) ,2))))*1000 Forecasted_Margin
FROM dm01.sales_data sda,
dm01.location loc,
dm01.T_EP_MIS_SUBDIS sub,
DM01.items itm,
DM01.T_EP_ITEM epi
WHERE sda.item_id =itm.item_id
AND itm.T_EP_ITEM_EP_ID =epi.T_EP_ITEM_EP_ID
AND sda.location_id = loc.location_id
AND loc.T_EP_MIS_SUBDIS_EP_ID = sub.T_EP_MIS_SUBDIS_EP_ID
AND sda.sales_date >= add_months (sysdate , -5)
AND sda.Sales_date <= add_months (sysdate , -1)
AND sub.MIS_SUBDIS_DESC = 'Germany Sub-Dist'
GROUP BY sda.sales_date
)
;
SELECT T1.Sales_Date,
T1.Net_Need,
T1.Proj_on_Hand,
T1.Safety_Stock,
T1.Cost_Of_Forecast,
T1.Revenue_Of_Forecast,
T1.Forecasted_Margin,
ROUND(((12 * (T2.Cost_of_forecast + T3.Cost_of_forecast + T4.Cost_of_forecast ))/(T2.Proj_on_hand + T3.Proj_on_hand + T4.Proj_on_hand)),2) Turn_Rate
FROM temp_subd_net_need T1,
temp_subd_net_need T2,
temp_subd_net_need T3,
temp_subd_net_need T4
WHERE T1.Sales_Date = add_months (T2.Sales_date , -1)
AND T1.Sales_Date = add_months (T3.Sales_date , -2)
AND T1.Sales_Date = add_months (T4.Sales_date , -3)

4. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
I don't know whether code you posted does what you wanted (but, as you said it does, fine). However: I'd suggest you NOT to use a global temporary table - it is unnecessary (is your background SQL Server, perhaps, and now you switched to Oracle?).

The same could have been done with an inline view or - maybe even better - a WITH factoring clause. Here are both examples:
Code:
```SQL> -- Inline view
SQL> select sum(s.salary) sum_sal
2  from (select e.sal salary
3        from emp e
4       ) s;

SUM_SAL
----------
40225

SQL> -- With factoring clause
SQL> with salaries as
2    (select e.sal salary
3     from emp e
4    )
5  select sum(s.salary) sum_sal
6  from salaries s;

SUM_SAL
----------
40225

SQL>```
It means that you'd put the GTT SELECT statement into one of these forms (either an inline view, or a WITH factoring clause) and avoid an unnecessary intermediate table.

5. Registered User
Join Date
Dec 2012
Posts
5
Originally Posted by Littlefoot
I don't know whether code you posted does what you wanted (but, as you said it does, fine). However: I'd suggest you NOT to use a global temporary table - it is unnecessary (is your background SQL Server, perhaps, and now you switched to Oracle?).

I do not have any background in sql, I am a chemical engineer, I work at a fortune 500 company and my job is to make sure the oracle implementation answers all of the needs of operations.

I have been working with sql less than 2 months.

Originally Posted by Littlefoot

The same could have been done with an inline view or - maybe even better - a WITH factoring clause. Here are both examples:
Code:
```SQL> -- Inline view
SQL> select sum(s.salary) sum_sal
2  from (select e.sal salary
3        from emp e
4       ) s;

SUM_SAL
----------
40225

SQL> -- With factoring clause
SQL> with salaries as
2    (select e.sal salary
3     from emp e
4    )
5  select sum(s.salary) sum_sal
6  from salaries s;

SUM_SAL
----------
40225

SQL>```
It means that you'd put the GTT SELECT statement into one of these forms (either an inline view, or a WITH factoring clause) and avoid an unnecessary intermediate table.

Before I posted here I tried with, and it is to slow in this case. I will try and give the inline view a try, I just googled it and it looked like it might work.

Thanks for the help.

6. Registered User
Join Date
Dec 2012
Posts
5
The problem with using an inline view in this case is that I have to alias the inline view 4 times to join the inline view to itself.

SELECT T1.Sales_Date,
T1.Proj_on_Hand,
T1.Cost_Of_Forecast,
ROUND(((12 * (T2.Cost_of_forecast + T3.Cost_of_forecast + T4.Cost_of_forecast ))/(T2.Proj_on_hand + T3.Proj_on_hand + T4.Proj_on_hand)),2) Turn_Rate

FROM temp_subd_net_need T1,
temp_subd_net_need T2,
temp_subd_net_need T3,
temp_subd_net_need T4

WHERE T1.Sales_Date = add_months (T2.Sales_date , -1)
AND T1.Sales_Date = add_months (T3.Sales_date , -2)
AND T1.Sales_Date = add_months (T4.Sales_date , -3)

My question is, can i create 4 alias's off of the same inline view, or do I have to run the inline view 4 different times?

7. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113