How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column 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
In addition to other answers - and to make it clear that subqueries or CTEs are not needed here. We can have the same result without them. Use whatever is more clear to you:

select
     s.store_id,
     max(s.rating) - min(s.rating) as difference,
     min(s.rating) as min_rating,
     max(s.rating) as max_rating
from
     storeRatings as s
group by
     s.store_id
  ;