CODEWITHSUNDEEP
phpmysqlweb
Margus
Margus

Reputation: 83

multiple SUM from multiple tables to one query

I have 2 tables: incomes & outgo both containing information about transactions. I'd like to run a report to show the overall flow of money based on the totals in the tables, I currently do this manually by using the following queries:

1) SELECT amount, SUM(amount) AS intotal FROM incomes
2) SELECT amount, SUM(amount) AS outtotal FROM outgo

as an example, lets say intotal is 500 and outtotal is 300

I'd like a single query that gets both summaries and subtracts the outtotal amount from the intotal amount with a result of 200 in this case. can anyone point me in the right direction or help me with syntax?

Upvotes: 0

Views: 27

Answers (2)

6be709c0
6be709c0

Reputation: 8441

Try

SELECT
    (SELECT amount, SUM(amount) AS intotal FROM incomes) AS a,
    (SELECT amount, SUM(id) AS outtotal FROM outgo) AS b

And use with a.intotal and b.outtotal

Or directly with

SELECT 
    ((SELECT SUM(amount)FROM incomes) - (SELECT SUM(amount)FROM outgo)) AS total

Upvotes: 1

Kenney
Kenney

Reputation: 9093

How about this:

SELECT
   (SELECT SUM(amount) FROM incomes)
   -
   (SELECT SUM(amount) FROM outgo)

Upvotes: 1

Related Questions