Sanju Menon Sanju Menon - 3 months ago 16
MySQL Question

Subtraction between two mysql subqueries

Iam trying to subtract two sub queries. The two tables are listed below

COM_PAYMENTS

+++++++++++++++++++
tender_id | amount
+++++++++++++++++++
10 | 200
10 | 345
22 | 18
25 | 45

COM_RECEIPTS

+++++++++++++++++++
tender_id | amount
+++++++++++++++++++
10 | 10
10 | 15
22 | 13


What iam trying to achieve in a query is First i need to find the sum of amount of tender_ids of both COM_PAYMENTS and COM_RECEIPTS and the total amount of COM_PAYMENTS need to be subtracted from total amount of COM_RECEIPTS.

Example: The out put in this case should be:

tender_id | Total

10 520 (200 + 345 = 545, 10 + 15 = 25, 545 - 25= 520)
22 5 (18 - 13)
25 45 ( Since COM_PAYMENTS doesnt have any recrds)


This is what i have tried, but where ever data is there in COM_RECEIPTS only its showing the total, for others its not subtracting its showing blank:

(select
SUM(com_payments.amount)
FROM com_payments
WHERE view_sales_report.tender_id = com_payments.tender_id) -
(select
SUM(com_receipts.rec_amt)
FROM com_receipts
WHERE view_sales_report.tender_id = com_receipts.tender_id)

Answer
SELECT tender_id
     , SUM(amount) total
  FROM
     ( SELECT tender_id, amount FROM com_payments
        UNION ALL
       SELECT tender_id, amount*-1 FROM com_receipts
     ) x
 GROUP 
    BY tender_id;

Moving forward, I would suggest you have only one table, which records all transactions - payments and receipts - with a transaction_id and a column recording the type of transaction.