CODEWITHSUNDEEP
mysqlsqldatabase
FeRcHo
FeRcHo

Reputation: 1151

get total amount of expenses per month in MySQL

I am currently working with 2 tables, expenses and income. To keep the structure simple and can see it, this is the fiddle: http://sqlfiddle.com/#!9/256cd64/2

The result I need from my query is the total amount for each month of the current year, for this and tried something like this:

select sum(e.amount) as expense, DATE_FORMAT(e.date,'%m') as month 
from expenses e
where year(e.date) = 2019
group by month

My problem with this is that it only takes me the months where there was registration and I would like it to take 12 months whether or not they have a registration, in the case that they did not return 0 as a total amount.

At the moment I am working with the table of expenses but I would like to have a single query that returns the monthly expenses and income, this is an example of the final output that I would like to obtain:

|   Month | Expense| Incomes |
|---------|--------|---------|
|       01|      0 |       0 |
|       02|   3000 |    4000 |
|       03|   1500 |    5430 |
|       04|  2430  |    2000 |
|       05|  2430  |    1000 |
|       06|  2430  |    1340 |
|       07|     0  |    5500 |
|       08|  2430  |    2000 |
|       09|  1230  |    2000 |
|       10|  8730  |    2000 |
|       11|  2430  |    2000 |
|       12|  6540  |    2000 |

Upvotes: 0

Views: 2074

Answers (4)

Piyush Shukla
Piyush Shukla

Reputation: 254

SELECT
    t1.month,
    COALESCE(t2.amount, 0) AS expenses,
    COALESCE(t3.amount, 0) AS incomes
FROM
(
    SELECT 1 AS month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12
) t1
LEFT JOIN
(
    SELECT MONTH(date) AS month, SUM(amount) AS amount
    FROM expenses
    GROUP BY MONTH(date)
) t2
    ON t1.month = t2.month
LEFT JOIN
(
    SELECT MONTH(date) AS month, SUM(amount) AS amount
    FROM incomes
    GROUP BY MONTH(date)
) t3
    ON t1.month = t3.month
ORDER BY
    t1.month;

Upvotes: 0

daShier
daShier

Reputation: 2116

Try changing your sum(e.amount) as expense to: COALESCE(sum(e.amount),0) as expense

The COALESCE function returns the first non NULL value.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to generate the month values and then use left join to match to expenses:

select coalesce(sum(e.amount), 0) as expense, m.month
from (select '01' as month union all
      select '02' as month union all
      select '03' as month union all
      select '04' as month union all
      select '05' as month union all
      select '06' as month union all
      select '07' as month union all
      select '08' as month union all
      select '09' as month union all
      select '10' as month union all
      select '11' as month union all
      select '12' as month
     ) m left join
     expenses e
     on year(e.date) = 2019 and
        DATE_FORMAT(e.date,'%m') = m.month
group by m.month;

Here is a db<>fiddle.

As for income, you should ask another question about that.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can use MONTH to get month value from your date column and then GROUP BY them to get your desired output as below-

SELECT SUM(e.amount) AS expense, 
MONTH(e.date) AS month    
FROM expenses e
WHERE YEAR(e.date) = 2019
GROUP BY MONTH(e.date)

Upvotes: 0

Related Questions