date functions using Oracle

15 pts.
Tags:
Oracle
Oracle development
Oracle SQL
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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');
    69,465 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following