Press "Enter" to skip to content

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.

 

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *