Get Max value with respected column values within multiple unique groups-CodePudding

I'm trying to get all column in select statement with Max value

Here is my query

select city, 
       gender, 
       age_group,
       (count(id)/(select count(id) from table)) * 100 id_count_group_wise 
from table where state = 'true'
group by city, gender, age_group
order by city

Now above query will give me unique groups of (city, gender, age_group) with their respective count

How can I get the Max of (id_count_group_wise ) out of above query (where all records are already unique groups) with city, gender and age_group in select output?

CodePudding user response：

If the version of your DBMS is atleast 12c, then just use FETCH...ROW[S] clause such as

SELECT city,
       gender,
       age_group,
       (COUNT(id) / (SELECT count(id) FROM table)) * 100 id_count_group_wise
  FROM t
 WHERE state = 'true'
 GROUP BY city, gender, age_group
 ORDER BY id_count_group_wise DESC
 FETCH FIRST 1 ROW ONLY -- or replace ONLY with "WITH TIES" 
                        -- if the equal max values should be included in the result set

CodePudding user response：

Like this For Every Group

select city, 
       gender, 
       age_group,max(id_count_group_wise) as clmcount from (
select city, 
       gender, 
       age_group,
       (count(id)/(select count(id) from table)) * 100 id_count_group_wise 
from table where state = 'true'
group by city, gender, age_group

)a
group by city, gender, age_group
order by city

Like this For all Group

select city, 
       gender, 
       age_group,max(id_count_group_wise) over(order by age_group)  as clmcount from (
select city, 
       gender, 
       age_group,
       (count(id)/(select count(id) from table)) * 100 id_count_group_wise 
from table where state = 'true'
group by city, gender, age_group

)a
order by city

In order by you want related column replace

CodePudding user response：

You need to use analytic function max applied to your calculation:

create table t as
select
  level as id,
  'City ' || mod(level, 5) as city,
  decode(mod(level, 2), 1, 'M', 'F') as gender,
  30   mod(level, 4) as age_group,
  case
    when level > 30
    then 'false'
    else 'true'
  end as state
from dual
connect by level < 40

select /* gather_plan_statistics*/
  city, 
  gender, 
  age_group,
  trunc(count(id)/cnt.cnt * 100, 3) id_count_group_wise,
  trunc(max(count(id)/cnt.cnt) over() * 100, 3) as max_
from t
  cross join (
    select count(id) as cnt
    from t
  ) cnt
where state = 'true'
group by city, gender, age_group, cnt.cnt
order by city

CITY   | GENDER | AGE_GROUP | ID_COUNT_GROUP_WISE |  MAX_
:----- | :----- | --------: | ------------------: | ----:
City 0 | F      |        30 |               2.564 | 5.128
City 0 | F      |        32 |               5.128 | 5.128
City 0 | M      |        31 |               5.128 | 5.128
City 0 | M      |        33 |               2.564 | 5.128
City 1 | F      |        30 |               2.564 | 5.128
City 1 | F      |        32 |               5.128 | 5.128
City 1 | M      |        31 |               5.128 | 5.128
City 1 | M      |        33 |               2.564 | 5.128
City 2 | F      |        30 |               2.564 | 5.128
City 2 | F      |        32 |               5.128 | 5.128
City 2 | M      |        31 |               2.564 | 5.128
City 2 | M      |        33 |               5.128 | 5.128
City 3 | F      |        30 |               5.128 | 5.128
City 3 | F      |        32 |               2.564 | 5.128
City 3 | M      |        31 |               2.564 | 5.128
City 3 | M      |        33 |               5.128 | 5.128
City 4 | F      |        30 |               5.128 | 5.128
City 4 | F      |        32 |               2.564 | 5.128
City 4 | M      |        31 |               5.128 | 5.128
City 4 | M      |        33 |               2.564 | 5.128

db<>fiddle here