Select min and max values while grouped by a third column

Question

I have a table with campaign data and need to get a list of 'spend_perc' min and max values while grouping by the client_id AND timing of these campaigns.

sample data being:

camp_id  | client_id | start_date | end_date   |   spend_perc       
    7257 |    35224  | 2017-01-16 | 2017-02-11 | 100.05 
    7284 |    35224  | 2017-01-16 | 2017-02-11 | 101.08 
    7308 |    35224  | 2017-01-16 | 2017-02-11 |  101.3  
    7309 |    35224  | 2017-01-16 | 2017-02-11 |  5.8    
    6643 |    35224  | 2017-02-08 | 2017-02-24 |  79.38  
    6645 |    35224  | 2017-02-08 | 2017-02-24 |  6.84   
    6648 |    35224  | 2017-02-08 | 2017-02-24 |  100.01 
    6649 |    78554  | 2017-02-09 | 2017-02-27 |  2.5    
    6650 |    78554  | 2017-02-09 | 2017-02-27 |  18.5   
    6651 |    78554  | 2017-02-09 | 2017-02-27 |  98.5

what I'm trying to get is the rows with min and max 'spend_perc' values per each client_id AND within the same campaign timing (identical start/end_date):

camp_id  | client_id | start_date | end_date  |   spend_perc       
    7308 |    35224  | 2017-01-16 | 2017-02-11 |  101.3  
    7309 |    35224  | 2017-01-16 | 2017-02-11 |  5.8    
    6645 |    35224  | 2017-02-08 | 2017-02-24 |  6.84   
    6648 |    35224  | 2017-02-08 | 2017-02-24 |  100.01 
    6649 |    78554  | 2017-02-09 | 2017-02-27 |  2.5    
    6651 |    78554  | 2017-02-09 | 2017-02-27 |  98.5

Vao Tsun · Accepted Answer · 2017-02-09 15:10:17Z

1

smth like:?

with a as 
(select distinct 
camp_id,client_id,start_date,end_date,max(spend_perc) over (partition by start_date,end_date),min(spend_perc) over (partition by start_date,end_date)
from tn
)
select camp_id,client_id,start_date,end_date,case when spend_perc=max then max when spend_perc = min then min end spend_perc
from a
order by camp_id,client_id,start_date,end_date,spend_perc

answered Feb 9, 2017 at 15:10

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Travers · Accepted Answer · 2017-02-09 15:09:36Z

1

I think you will want to get rid of the camp_id field because that will be meaningless in this case. So you want something like:

 SELECT client_id, start_date, end_date, 
        min(spend_perc) as min_spend_perc, max(spend_perc) as max_spend_perc
   FROM mytable
  GROUP BY client_id, start_date, end_date;

Group by the criteria you want to, and select min and max as columns per unique combination of these values (i.e. per row).

answered Feb 9, 2017 at 15:09

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

1 Comment

pavelpok Over a year ago

Thanks Chris, I do need the camp_id in the results, though.

Collectives™ on Stack Overflow

Select min and max values while grouped by a third column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related