Oracle 11g get data from 3 tables-CodePudding

I am trying to get a record from one table and on the basis of that record sum of quantity in another table which has multiple record and then join thrid table to get the price and multiply price with quantity.

order

id	status	date
1	1	25-12-2022

orderitem

id	pid	qty	Uom(unit of measure)
1	100	5	KG
1	101	10	KG
1	102	15	KG

productstable

pid	price	Uom(unit of measure)
100	1	KG
101	2	KG
102	3	KG

OUTPUT:

date	quantity	Price
25-12-2022	30	70

1-Get record from order

2-Sum quantity of that id

3-get price of product id and multiply it with sum quantity in step 2

CodePudding user response：

Sample data:

SQL> with
  2  t_order (id, status, datum) as
  3    (select 1, 1, date '2022-12-22' from dual),
  4  orderitem (id, pid, qty) as
  5    (select 1, 100, 5 from dual union all
  6     select 1, 101, 10 from dual union all
  7     select 1, 102, 15 from dual
  8    ),
  9  productstable (pid, price) as
 10    (select 100, 1 from dual union all
 11     select 101, 2 from dual union all
 12     select 102, 3 from dual
 13    )

Query begins here:

 14  select o.datum,
 15    sum(i.qty) quantity,
 16    sum(i.qty * p.price) price
 17  from t_order o join orderitem i on i.id = o.id
 18                 join productstable p on p.pid = i.pid
 19  group by o.datum;

DATUM        QUANTITY      PRICE
---------- ---------- ----------
22-12-2022         30         70

SQL>

(I renamed invalid table (order) and column (date) names).

CodePudding user response：

As I understand the question you need the sum per order id ( 2-Sum quantity of that id ). If that is the case then you'll need order id for grouping the data:

SELECT      o.ID "ORDER_ID", o.ORDER_DATE,
            Sum(oi.QTY) "QTY", Sum(oi.QTY * p.PRICE) "AMOUNT"
FROM        order_tbl o
INNER JOIN  order_items oi ON(oi.ID = o.ID)
INNER JOIN  products p ON(p.PID = oi.PID)
GROUP BY    o.ID,  o.ORDER_DATE
ORDER BY    o.ID

With your sample data (added a few more rows):

with
    order_tbl (ID, STATUS, ORDER_DATE) AS
        (   
            Select 1, 1, To_Date('2022-12-25', 'yyyy-mm-dd') From Dual Union All
            Select 2, 1, To_Date('2022-12-25', 'yyyy-mm-dd') From Dual Union All
            Select 3, 1, To_Date('2022-12-25', 'yyyy-mm-dd') From Dual 
        ),
    order_items (ID, PID, QTY, UOM) AS
        (
            Select 1, 100,  5, 'KG' From Dual Union All
            Select 1, 101, 10, 'KG' From Dual Union All
            Select 1, 102, 15, 'KG' From Dual Union All
      --  added order 2
            Select 2, 101, 15, 'KG' From Dual Union All
            Select 2, 100, 15, 'KG' From Dual Union All
      --  added order 3
            Select 3, 102, 10, 'KG' From Dual 
        ),
    products (PID, PRICE, UOM) as
        (
            Select 100, 1, 'KG' From Dual Union All
            Select 101, 2, 'KG' From Dual Union All
            Select 102, 3, 'KG' From Dual
        )

R e s u l t :

ORDER_ID	ORDER_DATE	QTY	AMOUNT
1	25-DEC-22	30	70
2	25-DEC-22	30	45
3	25-DEC-22	10	30

And if you need dayly subtotals - u can use MODEL clause like here...

SELECT  
    ORDER_ID "ORDER_ID", ORDER_DATE, QTY, AMOUNT
FROM    ( SELECT        CAST(To_Char(o.ID) as VarChar2(20)) "ORDER_ID", o.ORDER_DATE,
                    Sum(oi.QTY) "QTY", Sum(oi.QTY * p.PRICE) "AMOUNT"
          FROM      order_tbl o
          INNER JOIN    order_items oi ON(oi.ID = o.ID)
          INNER JOIN    products p ON(p.PID = oi.PID)
          GROUP BY  o.ID,  o.ORDER_DATE
          ORDER BY  o.ID  )
MODEL 
    PARTITION BY  (ORDER_DATE)
    DIMENSION BY  (ORDER_ID)
    MEASURES      (QTY, AMOUNT)
    RULES     (  QTY['Total for Date']    = Sum(QTY)[ORDER_ID != 'Total for Date'],
                 AMOUNT['Total for Date'] = Sum(AMOUNT)[ORDER_ID != 'Total for Date'])
ORDER BY ORDER_DATE, ORDER_ID

which (with some more sample data [id = 4]) results as:

ORDER_ID	ORDER_DATE	QTY	AMOUNT
1	25-DEC-22	30	70
2	25-DEC-22	30	45
3	25-DEC-22	10	30
Total for Date	25-DEC-22	70	145
4	26-DEC-22	22	44
Total for Date	26-DEC-22	22	44