Home » SQL & PL/SQL » SQL & PL/SQL » query display like aging (11g )
query display like aging [message #684059] Tue, 30 March 2021 08:11 Go to next message
glmjoy
Messages: 171
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 Go to previous messageGo to next message
John Watson
Messages: 8560
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.
Re: query display like aging [message #684061 is a reply to message #684059] Tue, 30 March 2021 08:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3063
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.
Re: query display like aging [message #684062 is a reply to message #684059] Tue, 30 March 2021 10:14 Go to previous messageGo to next message
EdStevens
Messages: 1321
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.
Re: query display like aging [message #684063 is a reply to message #684059] Tue, 30 March 2021 15:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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
Smile

Re: query display like aging [message #684064 is a reply to message #684063] Wed, 31 March 2021 01:15 Go to previous messageGo to next message
glmjoy
Messages: 171
Registered: September 2011
Location: KR
Senior Member
Thanks a lot Guys for your efforts queries work for me
Re: query display like aging [message #684065 is a reply to message #684063] Wed, 31 March 2021 01:15 Go to previous message
glmjoy
Messages: 171
Registered: September 2011
Location: KR
Senior Member
Thanks Michel It works for me
Previous Topic: group by common string
Next Topic: Disperse credit notes between groups of invoices
Goto Forum:
  


Current Time: Tue Jun 22 00:47:02 CDT 2021