date functions using Oracle
Hi,
I need to display the benefits paid for the current year using SQl,but the year is filtered using the year which is previous year for the current year.the date is in 'yyyymm' format.
ex,
select year_mnth,benefits_paid
from tablename
where year_mnth in ('200603','200602','200601') note:it is in 'yyyymm' format
group by year_mnth
the benefits paid must be calculated for the year 200603 to 200601
but the display is as follows,
result
-------
year_mnth benefits_paid
200703 30000
200702 20000
200701 10000
Software/Hardware used:
Software/Hardware used:
ASKED:
May 12, 2008 6:59 AM
UPDATED:
September 22, 2008 8:59 PM
Answer Wiki:
no need for group by clause to see the whole years benefits paid.
Ans
I don't know that I precisely understand your question, but if you are wanting to group all months into a single year and display the amount of benefits paid for the year, this is a possible solution based on your example.
select substr(year_mnth,1,4) Year, sum(benefits_paid) Benefits
from tablename
where substr(year_mnth,1,4)='2006'
group by substr(year_mnth,1,4);
This would output:
Year Benefits
------ ------------
2006 60000
You can format this output in a variety of ways. I hope this helps.
To see all answers submitted to the Answer Wiki: View Answer History.
I guess the first answer was correct. It seems there is no need for the grouping.
select sum(benefits_paid)
from tablename
where year_mnth in (’200603′,’200602′,’200601′);