CODEWITHSUNDEEP
sqloracle-databasejoin
OrangeApple123
OrangeApple123

Reputation: 13

Sum Column in Joined Table and add as column SQL

So say I have two tables in Oracle SQL (not actually data but for ease should highlight my question)

Table1 that contains only Order_id and Order_quantity and Table2 that contains only Order_id and Order_price

Then I join them as follows

Select T1.Order_id,
T1.Order_quantity,
T2.Order_price,
T1.Order_quantity*T2.Order_price As "Order_amount",
Sum(Order_amount) As "Total_Sales"
from Table1 T1
inner join Table2 T2
on T1.Order_id = T2.Order_id

So essentially I want to have two extra columns, one as the product of columns from the two tables, and another as the sum of that column in my joined table(so every entry will be the same). However as you need to 

SUM(variable_name) From Table_Name

Can I assign a variable name to my new table and then refer to that. I tried the following but I'm getting a SQL command not properly ended error

Select T1.Order_id,
    T1.Order_quantity,
    T2.Order_price,
    T1.Order_quantity*T2.Order_price As "Order_amount",
    Sum(Order_amount) from New_Table As "Total_Sales"
    from (Table1 T1
    inner join Table2 T2
    on T1.Order_id = T2.Order_id) As New_Table

Thanks for any assistance, apologies as I have a pretty naive understanding of SQL at present

Upvotes: 1

Views: 89

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If your DBMS doesn't have a window function supports then you can use subquery instead 

select order_id, Order_quantity,
       (select t1.Order_quantity * t2.Order_price 
        from table2 t2
        where t2.Order_id = t1.Order_id) as Order_amount,
       (select sum(t1.Order_quantity * t2.Order_price) 
        from table2 t2
        where t2.Order_id = t1.Order_id) as Total_Sales
from table1 t1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think you just want a window function:

select T1.Order_id, T1.Order_quantity, T2.Order_price,
       T1.Order_quantity*T2.Order_price As order_amount,
       sum(T1.Order_quantity*T2.Order_price) over () As Total_Sales
from Table1 T1 inner join
     Table2 T2
     on T1.Order_id = T2.Order_id

You cannot re-use the alias order_amount in the select. You need to repeat the expression -- or use a subquery or CTE to define it.

Upvotes: 2

Related Questions