Question regarding subtracting two colums after being "summed"

[chunkII123]

Heres what I need to do, I'm trying to find the sum of two different columns. Here is the two differnt columns

SELECT SUM(amount)
FROM paymentsscheduled
WHERE membernumber = '15980';

and

SELECT SUM(amount)
FROM paymentsreceived
WHERE membernumber = '15980';

What I want MySQL to do is sum both columns individually, then take the 'amount' column from 'paymentsscheduled' and subtract the 'amount' column from 'paymentsreceived'.

Is this possible, and how might I do this?

Thanks ahead of time.

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[vacunita]

Possible yes, but we need more information. What is the relationship between both tables?

What field exists in both tables that ties them together?

Assuming there's some type of Id field in both you could do something like:

SELECT SUM(pr.amount) as Received, SUM(ps.amount) as Scheduled, (SUM(pr.amount)- SUM(ps.amount)) as Difference FROM paymentsreceived pr, paymentsscheduled ps WHERE pr.id=ps.id

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[r937]

don't need no relationship betwixt those tables

just sum them up separately, produce a result row, and do da calculation...

SELECT scheduled_amt
     , received_amt
     , scheduled_amt - received_amt AS diff
  FROM ( SELECT SUM(amount) AS scheduled_amt
           FROM paymentsscheduled
          WHERE membernumber = '15980' ) AS x
CROSS
  JOIN ( SELECT SUM(amount)AS received_amt
           FROM paymentsreceived
          WHERE membernumber = '15980' ) AS y

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon