Group by where category having max value

Question

Here is my ttable

 +---------------+------------------------------+---------------+--+                                                                                          
    | trading_year  |            sector            | total_volume  |                                                                                             
    +---------------+------------------------------+---------------+--+                                                                                          
    | 2010          | Consumer Discretionary       | 119621915300  |                                                                                             
    | 2012          | Consumer Discretionary       | 93805031400   |                                                                                             
    | 2014          | Consumer Discretionary       | 80018436200   |                                                                                             
    | 2016          | Consumer Discretionary       | 81834656900   |                                                                                             
    | 2010          | Consumer Staples             | 50710518800   |                                                                                             
    | 2012          | Consumer Staples             | 39340784200   |                                                                                             
    | 2014          | Consumer Staples             | 33188087300   |                                                                                             
    | 2016          | Consumer Staples             | 37538472900   |

i need to label the above data based on the max and min value of total_volume grouped by the sector

so my output should be like this

 +---------------+------------------------------+---------------+----------------+                                                                                          
    | trading_year  |            sector            | total_volume  |     value                                                                                        
    +---------------+------------------------------+---------------+--------------+                                                                                          
    | 2010          | Consumer Discretionary       | 119621915300  |   maxvalue                                                                                            
    | 2012          | Consumer Discretionary       | 93805031400   |   stable value                                                                                         
    | 2014          | Consumer Discretionary       | 80018436200   |   stable value                                                                                          
    | 2016          | Consumer Discretionary       | 81834656900   |   minvalue                                                                                          
    | 2010          | Consumer Staples             | 50710518800   |   maxvalue                                                                                          
    | 2012          | Consumer Staples             | 39340784200   |   stable value                                                                                          
    | 2014          | Consumer Staples             | 33188087300   |   minvalue                                                                                          
    | 2016          | Consumer Staples             | 37538472900   |   stable value

here is my query i tried but no luck

select *,
case when total_volume = max(total_volume) then 'maxvalue'
     when total_volume = min(total_volume) then 'minvalue'
     else 'stable value'
end value_type
from t
group by sector,trading_year

output from above query

+---------------+------------------------------+---------------+----------------+                                                                                          
    | trading_year  |            sector            | total_volume  |     value                                                                                        
    +---------------+------------------------------+---------------+--------------+                                                                                          
    | 2010          | Consumer Discretionary       | 119621915300  |   maxvalue                                                                                            
    | 2012          | Consumer Discretionary       | 93805031400   |   maxvalue                                                                                         
    | 2014          | Consumer Discretionary       | 80018436200   |   maxvalue                                                                                          
    | 2016          | Consumer Discretionary       | 81834656900   |   maxvalue                                                                                       
    | 2010          | Consumer Staples             | 50710518800   |   maxvalue                                                                                          
    | 2012          | Consumer Staples             | 39340784200   |   maxvalue                                                                                         
    | 2014          | Consumer Staples             | 33188087300   |   maxvalue                                                                                         
    | 2016          | Consumer Staples             | 37538472900   |   maxvalue

damientseng · Accepted Answer · 2020-09-17T09:11:49.947

Your SQL won't work here. Don't use group by since you wanna preserve all the rows. What we need for this is windowing and analytics.

select 
    t.*, 
    case 
        when total_volume = last_value(total_volume)  over w then 'maxvalue'
        when total_volume = first_value(total_volume) over w then 'minvalue'
        else 'stable value'
    end as value 
from t
window w as (partition by sector order by total_volume 
    rows between unbounded preceding and unbounded following)

Have a nice day :)

UPDATE: Yet another (simpler) way:

select 
    t.*, 
    case 
        when total_volume = max(total_volume) over w then 'maxvalue'
        when total_volume = min(total_volume) over w then 'minvalue'
        else 'stable value'
    end as value 
from t
window w as (partition by sector)

Note, without order by, the default window specification is rows between unbounded proceeding and unbounded following. Whereas with order by, the window specification is defaulted to rows between unbounded proceeding and current row.

Group by where category having max value

1 Answers1