How to Get Previous Months Data in Oracle Using SQL Query

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

You can use Oracle's add_months() function with negative value parameter to get previous months data in Oracle using SQL query. Below is the query example to get previous 12 months data:

select item_code, sum(sales_value) from sales_table
where sales_date >= add_months(sysdate, -12)
group by item_code;

Also, you can group by months as well:

select to_char(sales_date, 'mon') mnth, item_code, sum(sales_value) from sales_table
where sales_date >= add_months(sysdate, -12)
group by to_char(sales_date, 'mon'), item_code;

Do you want to make it more refined? OK, let's sort the result item and month wise. Below is the example to get last 12 months data in Oracle:

select item_code, to_char(sales_date, 'mon') mnth, 
to_number(to_char(sales_date, 'mm')) i_mnth,
sum(sales_value) from sales_table
where sales_date >= add_months(sysdate, -12)
group by item_code, to_char(sales_date, 'mon'), 
to_number(to_char(sales_date, 'mm'))
order by item_code, to_number(to_char(sales_date, 'mm'));

In above example you will get previous months data in Oracle group by item and month.

So if you want to get last 6 months data in Oracle using SQL then what will you do? Just use add_months() function with -6 value. That's all.

See also:

This Post Has 7 Comments

  1. Pratik dhok September 5, 2020

    If you are using sysdate means it will take values from that month also na not only last 3 months

    1. Vinish Kapoor September 5, 2020

      This is an example only. You can specify any other date, then it will get the data for last 3 months from that date.

    2. Pratik dhok September 13, 2020

      But if I want a query that will return only last 3 month data not current month data should include into results set.

    3. Vinish Kapoor September 14, 2020

      Try the below query:

      select item_code, sum(sales_value) from sales_table
where sales_date >= add_months(trunc(sysdate, 'month'), -3) 
and sales_date <= add_months(last_day(sysdate), -1)
group by item_code;

      Now this query will retrieve the data as follows:

      The first expression, sales_date >= add_months(trunc(sysdate, 'month'), -4) is equal to sales_date >= '01jun2020' and the second expression sales_date <= add_months(last_day(sysdate), -1) is equal to sales_date <= '31aug2020'.

      So you will get last three months data.

      Please let me know for any further questions.

  2. Amit May 5, 2021 
    select item_code, sum(sales_value) from sales_table
where sales_date >= add_months(sysdate, -12)
group by item_code;

    Its showing not a valid month. my date format is YYYYMMDD

    1. Vinish Kapoor May 6, 2021

      Try this:

      select item_code, sum(sales_value) from sales_table
where to_date(sales_date, 'yyyymmdd') >= add_months(to_date(sysdate, 'yyyymmdd'), -12)
group by item_code;
    2. Amit May 6, 2021

      Its also showing not a valid month

Comments are closed.