CODEWITHSUNDEEP
mysql
Sanju Menon
Sanju Menon

Reputation: 729

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)

Upvotes: 1

Views: 1603

Answers (3)

Strawberry
Strawberry

Reputation: 33945

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.

Upvotes: 3

Manchikanti Aishwarya
Manchikanti Aishwarya

Reputation: 290

SELECT SUM(DISTINCT pa.amount)-IFNULL(SUM(DISTINCT re.amount) , 0) FROM com_payments pa
LEFT OUTER JOIN com_receipts re ON pa.`tender_id` = re.`tender_id` GROUP BY pa.tender_id

Upvotes: 0

ignasi
ignasi

Reputation: 443

Probably something like this is what you need:

SELECT tender_id, SUM(amount) AS amount
FROM (
    SELECT tender_id, SUM(amount) AS amount
    FROM COM_PAYMENTS
    GROUP BY tender_id
    UNION ALL
    SELECT tender_id, SUM(amount * -1) AS amount
    FROM COM_RECEIPTS
    GROUP BY tender_id
  ) a
GROUP BY tender_id

Upvotes: 0

Related Questions