Query to calculate profits in a month

Question

I have to tables:

Incomes:

| date | incomeAmount |

Expenses:

| date | expenseAmount |

I would like to sum all my incomes in an especific month using: MONTH(date) = ?

I also need to sum all my expenses in this same month

Then subtract to obtain my profit in the month, with an output table similar to that:

| Month | TotalIncomes | TotalExpenses | Profit |

How can I create a SQL Query in order to do this? Thank you for all the help!

Teja · Answer 1 · 2016-10-02T22:21:29.393

SELECT I.Month AS Month,
       I.TotalIncomes AS TotalIncomes,
       E.TotalExpenses AS TotalExpenses,
       I.TotalIncomes - E.TotalExpenses AS Profit
  FROM ( SELECT EXTRACT(MONTH FROM DATE ) AS Month,
                SUM( I.incomeAmount ) AS TotalIncomes
           FROM Incomes
       )  I,
       (
         SELECT EXTRACT(MONTH FROM DATE ) AS Month,
                SUM( E.expenseAmount ) AS TotalExpenses
           FROM Expenses
       ) E
 WHERE I.Month = E.Month
;

score 0 · Answer 2 · answered Oct 02 '16 at 21:21

You can join the aggregated tables.

SELECT A.Month, TotalIncomes, TotalExpenses, TotalIncomes-TotalExpenses AS Profit
FROM (MONTH(Date) AS Month, SUM(incomeAmount) AS TotalIncomes
      FROM Incomes 
      GROUP BY MONTH(Date)
      ) A 
INNER JOIN (
      MONTH(Date) AS Month, SUM(expenseAmount) AS TotalExpenses
      FROM Incomes 
      GROUP BY MONTH(Date)
      ) B
 ON A.Month = B.Month

Query to calculate profits in a month

2 Answers2