Solved

# get count based on age in oracle

Posted on 2014-03-26
Medium Priority
535 Views
I want to get count of collections in the year 2013 for age group 16.

But in the where clause I cannot use max(coll_date)
If the donor has donated multiple times

select coll_date
from donations_don d,donors_don dn
where d.donor_id = dn.donor_id
and coll_date between '01-jan-2013' and '31-dec-2013'
and unit_id is not null
and dn.donor_id = 'DN20093654'
order by coll_date

COLL_DATE
3/1/2013
5/2/2013
7/5/2013
9/20/2013
11/22/2013

Now how do I calculate count for age group 16.

select count(*)
from donations_don d,donors_don dn
where d.donor_id = dn.donor_id
and coll_date between '01-jan-2013' and '31-dec-2013'
and unit_id is not null
and TRUNC(MONTHS_BETWEEN(max(coll_date), date_of_birth)/12)  = 16
0
Question by:anumoses

LVL 39

Accepted Solution

Geert G earned 2000 total points
ID: 39956253
why not use today to calculate the age ?

and TRUNC(MONTHS_BETWEEN(sysdate, date_of_birth)/12)  = 16

or the last date of the year at that time
or the date the donation was done  > colldate

and TRUNC(MONTHS_BETWEEN(coll_date, date_of_birth)/12)  = 16
0

LVL 6

Author Closing Comment

ID: 39956267
yes I did the same and before I could realize you had answered. Thanks
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.