Hi,
I have following requirement and seeking help to achieve this via SQL only (no PL/SQL block) -
Table A contains following columns -
1. Employee_ID
2. Employee_Age
3. Employee_Dept
The end user needs to provide following 3 prompt values -
1. Upper Age Limit (eg. 18)
2. Lower Age Limit (eg. 55)
3. Age Gap (eg. 7 )
Based on the user inputs, SQL should produce output as below -
Age Band Employee Count
------------------------------------------------------------
Under 18 8
18 - 24 11
25 - 31 11
32 - 38 15
39 - 45 14
46 - 52 15
53 - 54 4
55 Plus 71
No record should be returned for which the Employee Count is 0.
Please let me know if any further information is required.
Please help.
Thanks and Regards,
Rituraj
Grouping people outside the bounds is easy. Simply check if the age is less than the lower or greater than or equal to the upper.
It's the bit in between which gets interesting :)
You can do this by:
1 Subtracting the lower bound from the employee's age ( 22 - 18 ) = 4
2 Dividing this by the gap value ( 4 / 7 ) = 0.56...
3 Taking the floor of the division ( floor ( 0.56... ) = 0
4 Multiplying this back up by ( 0 * 7 ) = 0
5 Adding the lower bound back on ( 0 + 18 ) = 18
To find the upper bound, repeat the process. But add one to the floor calculated in step 3. And, because the upper bound could be part way through a group, return the least of this calculation and the upper bound. Then subtract one from the result.
Stick all the above in a case expression. And group by the results of it to get the counts.
All that remains is to get the rows in the correct order. You can do this by:
Mapping Under ... to -1. And the other values to 0. The sorting by the group value, e.g.:
order by case
when grp like 'U%' then -1
else 0
end, grp
Which gives something like:
with bounds as (
select 18 lwr, 55 upr, 7 gap from dual
), grps as (
select a.*,
case
when employee_age < lwr then 'Under ' || lwr
when employee_age >= upr then upr || ' Plus'
else to_char ( ( floor ( ( employee_age - lwr ) / gap ) * gap ) + lwr ) || '-' ||
to_char (
least (
upr,
( ( floor ( ( employee_age - lwr ) / gap ) + 1 ) * gap ) + lwr
) - 1
)
end grp
from a cross join bounds
order by employee_age
)
select grp, count (*),
min ( employee_age ), max ( employee_age )
from grps
group by grp
order by case
when grp like 'U%' then -1
else 0
end, grp;
GRP COUNT(*) MIN(EMPLOYEE_AGE) MAX(EMPLOYEE_AGE)
Under 18 8 12.5 17.5
18-24 11 18.5 24.5
25-31 11 25.5 31.5
32-38 15 32.25 38.5
39-45 14 39.5 45.5
46-52 15 46.25 52.5
53-54 4 53.5 54.5
55 Plus 71 55.5 100.5
PS - please tell me you're not storing ages in the table! You're calculating this from stored birth dates, right? And you're not hiring 8 year-olds? ;)