OraFAQ Forum: SQL & PL/SQL » query display like aging

Home » SQL & PL/SQL » SQL & PL/SQL » query display like aging (11g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query display like aging [message #684059]

Tue, 30 March 2021 08:11

glmjoy
Messages: 184
Registered: September 2011
Location: KR

Senior Member

CREATE TABLE ABC (EMP_NO VARCHAR2(5),AGE number(3))
insert into ABC (EMP_NO,AGE) values ('00001',15);
insert into ABC (EMP_NO,AGE) values ('00001',20);
insert into ABC (EMP_NO,AGE) values ('00001',22);
insert into ABC (EMP_NO,AGE) values ('00001',24);
insert into ABC (EMP_NO,AGE) values ('00001',28);
insert into ABC (EMP_NO,AGE) values ('00001',34);
insert into ABC (EMP_NO,AGE) values ('00001',38);
insert into ABC (EMP_NO,AGE) values ('00001',40);

I want query display as below like aging

10-20 21-30 31-40

2 3 3

Counting Employees between 10 to 20 as so on 30 to 40

[Updated on: Tue, 30 March 2021 08:14]

Report message to a moderator

Re: query display like aging [message #684060 is a reply to message #684059]

Tue, 30 March 2021 08:22

John Watson
Messages: 8803
Registered: January 2010
Location: Global Village

Senior Member

To group by ten year age ranges, you can use ROUND(age,-1)
Then you should be able to use PIVOT to convert the groups into column headings.

Report message to a moderator

Re: query display like aging [message #684061 is a reply to message #684059]

Tue, 30 March 2021 08:57

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

glmjoy wrote on Tue, 30 March 2021 09:11

Counting Employees between 10 to 20 as so on 30 to 40

Child labor is a federal offense Laughing

Also, why 10-20 but 21-30 and 31-40? I'll assume 11-20. Anyway, and what if company hires 45 years old? Do you want to display another column 41 - 50? Oracle PIVOT doesn't support dynamic pivot so you need to know all columns upfront:

select  count(case ceil(age / 10) when 2 then 1 end) "11-20",
        count(case ceil(age / 10) when 3 then 1 end) "21-30",
        count(case ceil(age / 10) when 4 then 1 end) "31-40"
  from  abc
/

     11-20      21-30      31-40
---------- ---------- ----------
         2          3          3

SQL>

SY.

Report message to a moderator

Re: query display like aging [message #684062 is a reply to message #684059]

Tue, 30 March 2021 10:14

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

unless your 'age' is a fixed at 'age as of some point in time' your data model is flawed. "Age" is constantly increasing. Instead of 'age' you should be storing date_of_birth as a DATE data type. Then you can always calculate 'age' as sysdate - date_of_birth.

Report message to a moderator

Re: query display like aging [message #684063 is a reply to message #684059]

Tue, 30 March 2021 15:15

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Having numbers per tens may be useful for an employer but other categories may also.
For instance, in France, it is forbidden to employ children less than 14, for age 14 and 15 you need the authorization of the parents and the factory inspector, for age 16 and 17 you only need the parents authorization. You can retire since 62 if you have subscribe sufficient quarters otherwise you have to wait 67. When you reach 70, your boss may ask you to retire (or "voluntarily" resign).
This leads to the following query with in the middle the tens and at the left and right ends the specials cases:

SQL> select count(case when age < 14 then age end) "<14",
  2         count(case when age between 14 and 15 then age end) "14-15",
  3         count(case when age between 16 and 17 then age end) "16-17",
  4         count(case when age between 18 and 19 then age end) "18-19",
  5         count(case when age between 10 and 19 then age end) "10-19",
  6         count(case when age between 20 and 29 then age end) "20-29",
  7         count(case when age between 30 and 39 then age end) "30-39",
  8         count(case when age between 40 and 49 then age end) "40-49",
  9         count(case when age between 50 and 59 then age end) "50-59",
 10         count(case when age between 60 and 69 then age end) "60-69",
 11         count(case when age between 60 and 61 then age end) "60-61",
 12         count(case when age between 62 and 66 then age end) "62-66",
 13         count(case when age between 67 and 69 then age end) "67-69",
 14         count(case when age >= 70 then age end) ">=70"
 15  from abc
 16  /
   <14  14-15  16-17  18-19  10-19  20-29  30-39  40-49  50-59  60-69  60-61  62-66  67-69   >=70
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
     0      1      0      0      1      4      2      1      0      0      0      0      0      0