Hi, Please help to display 1-25 & calculate %

[msr]

Hi,

I have a student table with their dob

sno name dob
--- ----- ---
1 john 30/06/1982
2 cary 12/12/1978
3 rao 01/06/1982

i need to display like this based on their age

age no %
---- --- --
1-25 2 66.66%
25-50 1 33.33%

i am using oracle 10g

How to do this only using one single query.

thanks in advance

msr

[davey23uk]

homework?

[msr]

i am learning jasper reports .............. help me pls

[cjard]

have a go at generating this data set:

sno name dob integer_age_div_by_25
--- ----- --- ---
1 john 30/06/1905 4
2 cary 12/12/1955 2
3 rao 01/06/1980 1


remember, there are 365.25 days in a year, and if you take someone's age in days and turn it into a years-based age, and then turn it into a 25years based age, then how old is a person whose 25year age is 2.99 ?

i.e. suppose one elephant year is 25 of our years (like dog years are 7 human years, so a dog age 10 is like a human aged 70)

if an animal is 74 human years old, how old is it in elephant years -> 2.99 or soemthing.. so its actually 2 elephant years old until it reaches its third elephant year birthday. im saying thios to make you think WHAT you have to do with any decimal point numbers you get.. i.e. dont ROUND them..


note also, you cant have age ranges like this:
1-25
25-50

because the first one is 24 years long, the second is 25. either make it 0-25 or make it 26-50
arranging inconsistency like that will drive you insane


once you get your record set how i describe you should be able to perform the necessary grouping, sums etc

remember, if a person is 0 elephant years old, they go in the range 0-25 human years, if they are 1 elephant year old they go in the range 25-50
how can you turn 0 into a string of '0-25' and 1 into a string of '25-50', and X into a string of 'Y-Z'

i.e. whats the relationship between X and Y, and X and Z? complete these formula:

Y = __________ (fill in the blank in terms of X)]
Z = __________ (fill in the blank in terms of X)]

[DaPi]

Use the supplied function MONTHS_BETWEEN and divide by 12 to determine ages.

And YES, your ranges musn't overlap. Where do you put someone who is exactly 25 today?

[WilliamR]

CREATE TABLE students
( sno INT PRIMARY KEY
, name VARCHAR2(20) NOT NULL
, dob DATE NOT NULL );

INSERT ALL
INTO students VALUES(1,'john',TO_DATE('30/06/1982','DD/MM/YYYY'))
INTO students VALUES(2,'cary',TO_DATE('12/12/1978','DD/MM/YYYY'))
INTO students VALUES(3,'rao',TO_DATE('01/06/1982','DD/MM/YYYY'))
SELECT * FROM dual;

SELECT age_range
     , COUNT(*)
     , 100 * RATIO_TO_REPORT(COUNT(*)) OVER () AS percent
FROM   ( SELECT CASE
                   WHEN age <= 25 THEN '1-25'
                   WHEN age <= 50 THEN '26-50'
                   ELSE '51+'
                END AS age_range
         FROM   ( SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,dob)/12) AS age
                  FROM   students )
       )
GROUP BY age_range;

AGE_RANGE   COUNT(*)    PERCENT
--------- ---------- ----------
1-25               2 66.6666667
26-50              1 33.3333333

Possibly cjard has another solution involving Elephant Years.