Member Avatar for anand01

my table design is

date     income  expenses  

2011-08-5   1000    500
2011-09-6   7000    400
2011-10-7   2000    300

I have daily expenses and Income in the above table.I need to retrieve monthly(sum of daily) income and expenses for particular year.
How should i write query for that .
Pls help me
Thanks in advance

  • 2 Contributors
  • 2 Replies
  • 578 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by anand01

Dani AI

Generated

— 's LEFT() trick will produce monthly totals, but it's safer and clearer to use MySQL date functions, handle NULLs, and filter by a year range so the optimizer can use an index. The examples below assume your date column is a proper DATE/DATETIME (or that you convert it with STR_TO_DATE if it's stored as text).

SELECT DATE_FORMAT(`date`, '%Y-%m') AS ym,
       SUM(COALESCE(income,0)) AS monthly_income,
       SUM(COALESCE(expenses,0)) AS monthly_expenses
FROM mytable
WHERE `date` >= '2011-01-01' AND `date` < '2012-01-01'
GROUP BY ym
ORDER BY ym;

If you prefer numeric year/month (useful for arithmetic or easier ORDER BY), group by YEAR() and MONTH():

SELECT YEAR(`date`) AS yr, MONTH(`date`) AS mon,
       SUM(COALESCE(income,0)) AS income,
       SUM(COALESCE(expenses,0)) AS expenses
FROM mytable
WHERE `date` BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY yr, mon
ORDER BY yr, mon;

Practical tips:

  • Make sure the column is a DATE/DATETIME. If it's stored as text, use STR_TO_DATE() to convert (match the correct format string).
  • Use the half-open range (>= start AND < next_year_start) to let an index on date be used. Avoid wrapping the date column in functions in the WHERE clause.
  • Wrap sums with COALESCE(...,0) so months with only NULLs return 0.
  • If you need months with zero totals included, left-join an aggregated result to a month-calendar table (or a derived list of 12 months).
  • Rename or always escape the date column (backticks) since DATE is a keyword in some contexts.

These adjustments make the query more robust, faster on large tables, and clearer to maintain.

Recommended Answers

All 2 Replies

Member Avatar for rch1231

Hello,

This is quick and dirty since I don't know the real field names or table name but something like this should work:

select 
left(`date`,7) as 'Year-Month',
sum(income) as Monthly_Income,
sum(expenses) as Monthly Expenses
from 
mytable
group By 'Year-Month'

You should get a monthly total on each line.

Member Avatar for anand01

thanks rch1231

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.