Search code examples
joinpymssql

mysql query for a situation?

I have two tables,

OrderTable(orderid,ordersum)

and OrderPayments(orderid,paidamount).

OrderTable

orderid ordersum

ORD123456 40,000.00

ORD789987 30,000.00

OrderPayments

orderid paidamount

ORD123456 10,000.00

ORD123456 20,000.00

ORD123456 10,000.00

ORD789987 28,000.00

For an order , payment can be made in installments.

I have to get the output as

orderid ordersum paidamount

ORD123456 40,000.00 40,000.00

ORD789987 30,000.00 28,000.00

I have done using view.

create view op1 as select orderid,sum(paidamount) as "pamount" from orderpayments group by orderid;

then i used inner join query b/w ordertable and the view (op1).

I want it to be done in a single query, not to use view / any other table.. Is that possible ? suggest me ?

Solution

  • Use inner query. This is example for join statement

    select * from OrderTable
inner join (
    select 
        orderid, sum(paidamount) as "pamount" 
    from orderpayments 
    group by orderid
) as tmp using (orderid)