I have four tables
The first one
Table Name Contract_master Contract_details Sales_Master Sales_Detail
Columns Contract_no PK Item_code Sales_No Item_Code
Contract_ date Price Sales_date Item_description
Quotation_no Contract_Quantity contract_no FK Item_Price
Contract_no FK invoice_no PK Item_quantity
invoice_no FK
Contract master table including
Contract_no Contract_date Quotation_no
1234 21-AUG-2017 123
54564 21-AUG-2017 545
Contract_details table including
Contract_no item_code contract_quantity Price
1234 LLOKJI 500 2500
1234 LJUHTT 300 3000
54564 HYGTTR 8 72
sales_master table including
invoice_no sales_no sales_date contract_no
311067 21703377 21-AUG-2017 1234
311066 21703376 21-AUG-2017 1234
311065 21703375 21-AUG-2017 54564
sales_Details table including
invoice_no Item_price Item_quanttity
311067 LLOKJI 50
311067 LJUHTT 50
311066 LLOKJI 200
311066 LJUHTT 50
311065 HYGTTR 5
I want to write query that retrieve the contract number with total quantity of the contract number and the sales no , date , with sum item_quantity under Sales_No
I want to the output to be like this
Sales_No Sales_date Contract_no sum(item_quantity) sum(contract_ Quantity)
21703377 21-AUG-2017 1234 100 800
21703376 21-AUG-2017 1234 250 800
21703375 21-AUG-2017 54564 5 8
I wrote this query
select DISTINCT (SM.SALES_NO) , SM.SALES_DATE, SUM(TD.QUANTITY) ,CM.CONTRACT_NUMBER , SUM(CD.contract_ Quantity)
FROM sales_master SM , SALES_DETAIL SD , CONTRACT_DETAIL CD , CONTRACT_MASTER CM
WHERE SM.CONTRACT_NO = CM.CONTRACT_NO
and SM.INVOICE_NO = SD.INVOICE_NO
AND CM.CONTRACT_NO = CD.CONTRACT_NO
GROUP BY SM.SALES_NO , SM.SALES_DATE ,CM.CONTRACT_NUMBER;
AND I GOT THIS with wrong contract quantity number
Sales_No Sales_date Contract_no sum(item_quantity) sum(contract_ Quantity)
21703377 21-AUG-2017 1234 100 1600
21703376 21-AUG-2017 1234 250 1600
21703375 21-AUG-2017 54564 5 8
I tried to use over partition by , union and left outer join but nothing working , please help