| urgent!! [message #168757] |
Sat, 22 April 2006 02:18  |
amit_garg3
Messages: 22
Registered: January 2006
Location: bangalore
|
Junior Member |
|
|
Hi All
I wannt ot know how to calulate age of the emp having birthdate and arrangr them in group like 18-30,30-40,40-50.
I have four group of emp and their birthdate.Now i have to calculate the age and arrange them in group of age 18 to 30,30 to 40 & so on and to make report.
Group 18-30 30-40
A 20 30
B 10 40
C 25 52
Regards
Amit
|
|
|
|
| Re: urgent!! [message #168820 is a reply to message #168757] |
Sun, 23 April 2006 08:28  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Amit,
(a) although intriguing, your subject ("urgent") is not very descriptive, it would help to get your answer, if you use a better one.
(b) this seems like an SQL question to me, so actually your on the wrong node.
Okay, now for the answer:
From your example, I get that you need counts of the people in every group, is that correct? If so, then you can use a case when statement (I use hire_date in the example, since there is no birthdate in emp). I like inline queries a lot, as you can see, but if you like you can do it all in one query, reason I'm using subqueries is that you can try each step seperately:
SELECT COUNT(sub1.employee_id) numb_emp
,sub1.age_group
FROM (SELECT sub.employee_id
,CASE
WHEN sub.age BETWEEN 1 AND 5 THEN
'1-5'
WHEN sub.age BETWEEN 6 AND 10 THEN
'6-10'
WHEN sub.age > 11 THEN
'over 10'
ELSE
'don''t know'
END age_group
FROM (SELECT e.employee_id
,trunc((months_between(SYSDATE
,e.hire_date)) / 12) age
FROM employees e) sub) sub1
GROUP BY sub1.age_group
Regards,
Sabine
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
