Home > Enterprise >  Query for Joining two tables to get result from 2nd table in reference
Query for Joining two tables to get result from 2nd table in reference

Time:12-15

I have two tables Order and Transaction

Order Table

id OrderId TransId OrderDate
1 54 11551 01-12-2021
2 55 11552 02-12-2021
3 56 11553 02-12-2021
4 57 11554 05-12-2021
5 58 11555 08-12-2021
6 59 11556 09-12-2021

Transaction Table

id TransId OrderId PaymentMethod Amount
1 11551-1 54 Cash 1000
2 11552-1 55 Cash 500
3 11552-2 55 Card 200
4 11553-1 56 Cash 500
5 11553-2 56 Card 200
6 11553-3 56 UPI 100

Here we have 3 Transaction methods Cash, Card and UPI.

I want to get total Amount collected using Cash as PaymentMethod done for period 01-12-2021 to 08-12-2021 in orderDate of Order Table

CodePudding user response:

select t.paymentMethod, sum(t.amount) from tbl_order o
inner join tbl_transaction t on o.orderId = t.orderId 
where t.paymentMethod = 'CASH' and o.orderDate between start_date and end_date
group by t.paymentMethod;   
  • Related