CODEWITHSUNDEEP
sqlsql-servert-sqljoinaggregation
Sai
Sai

Reputation: 61

Joining tables and aggregation/sub queries

I have 2 tables as show below.

Table1

Order ID Item_code Sales_Price Qty_ordered Total Qty shipped
1000 111 10 5 $50 1
1000 222 20 10 $200 2
1000 333 30 15 $450 0

I have another table that stores only the details of how much was invoiced (i.e. how much we shipped)

Table2 (because we shipped only 10x1 and 20x2 = $50)

Order ID Invoice_total
1000 $50

I wrote the following query,

select T1.Order_ID, 
       sum(T1.Qty_Ordered) as Qty_Ordered, 
       sum(T1.Total) as Total_Amt_ordered, 
       sum(T1.Qty_shipped) as Qty_Shipped, 
       sum(T2.Invoice_total) 
  from T1 join 
       T2 on T1.Order_ID = T2.Order_ID

This query gives me the following output, (It is adding $50 to all the rows of T1 Orders).

Order ID Qty_ordered Total Qty shipped Invoice_total
1000 30 $700 3 $150

Whereas now, I want my output to be as:

Order ID Qty_ordered Total Qty shipped Invoice_total
1000 30 $700 3 $50

(because we shipped only $50)

What changes should I make to my query? I know I can just hard code it but my database has 1000's of orders and 1000's of Half shipped Orders. I want to keep track of Shipped $ (Invoiced $) for all the orders.

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand correctly, you want:

select T2.Order_ID, T2.Invoice_total, 
       sum(T1.Qty_Ordered) as Qty_Ordered, 
       sum(T1.Total) as Total_Amt_ordered, 
       sum(T1.Qty_shipped) as Qty_Shipped, 
from T2 join 
     T1
     on T1.Order_ID = T2.Order_ID
group by T2.Order_ID, T2.Invoice_total;

That is, you don't want to aggregate Invoice_total. You just want it to be a group by key.

Upvotes: 1

Related Questions