2019-06-13
How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column
dba.stackexchange
Question

I have a query that is returning all RATINGS for STORES and a RATING_DATE. There isn't a fixed amount of ratings, it could be from one to unlimited rows that are returns that have the same STORE_ID value.

I'm trying to find the min, max, and difference for the STORE_ID, like in the example below:

Example data:

STORE_ID RATING_DATE    RATING
-------- -----------    ------
34       2015-01-20     4.0
34       2016-01-01     4.14
34       2017-01-01     4.56
34       2018-01-01     4.78
46       2015-01-01     3.5
46       2017-01-01     3.7
46       2016-01-01     3.95

Desired results:

STORE_ID DIFFERENCE MIN  MAX
-------- ---------- ---  ----
34      .78         4.0  4.78
36      .45         3.5  3.95
Answer
1

Try this

create table #StoreRatings (
store_id    int,
rating_date date,
rating  float

)

insert into #storeRatings values (30,'2019-05-01',4.0)
insert into #storeRatings values (30,'2019-05-02',4.7) 
insert into #storeRatings values (31,'2019-04-01',1.7)
insert into #storeRatings values (31,'2019-05-01',4.7)

;with t1 as (
select s.store_id,
   min(s.rating) as min_rating,
   max(s.rating) as max_rating
 from #storeRatings s
group by s.store_id
)

 select t1.store_id,
     abs(t1.min_rating - t1.max_rating) as difference,
      t1.min_rating,
      t1.max_rating
 from t1
How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column
See more ...