Home » SQL » How to Get Previous Months Data in Oracle Using SQL Query

How to Get Previous Months Data in Oracle Using SQL Query

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:

7 thoughts on “How to Get Previous Months Data in Oracle Using SQL Query”

    1. 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. But if I want a query that will return only last 3 month data not current month data should include into results set.

    3. 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.

  1. 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. 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;
      

Comments are closed.