By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table T as
select
1 as state
, 1000 as population
, 250 as youngerthan18
, 600 as between18and50
, 150 as over50
from dual union all
select 2, 4200, 400, 300, 3500 from dual ;
2 rows affected
select * from T ;
| STATE | POPULATION | YOUNGERTHAN18 | BETWEEN18AND50 | OVER50 |
|---|---|---|---|---|
| 1 | 1000 | 250 | 600 | 150 |
| 2 | 4200 | 400 | 300 | 3500 |
select distinct
state
, population
, max( quantity ) over ( partition by state ) maxq
, min( quantity ) over ( partition by state ) minq
, first_value ( agegroup ) over ( partition by state order by quantity desc ) biggest_group
, first_value ( agegroup ) over ( partition by state order by quantity ) smallest_group
from T
unpivot (
quantity for agegroup in (
youngerthan18 as 'youngest'
, between18and50 as 'middleaged'
, over50 as 'oldest'
)
)
order by state
;
| STATE | POPULATION | MAXQ | MINQ | BIGGEST_GROUP | SMALLEST_GROUP |
|---|---|---|---|---|---|
| 1 | 1000 | 600 | 150 | middleaged | oldest |
| 2 | 4200 | 3500 | 300 | oldest | middleaged |