CODEWITHSUNDEEP
sqlpostgresql
pavelpok
pavelpok

Reputation: 47

Select min and max values while grouped by a third column

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

Upvotes: 0

Views: 42

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51456

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

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26464

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).

Upvotes: 1

Related Questions