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
>Solution :
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;