Sql-server – How to get MIN, MAX and DIFFERENCE among rows with the same value in a certain column

sql server

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

Best Answer

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

Related Solutions

Sql-server – How to get the MAX row

Two usual methods: aggregate and ranking function.

The aggregate works on SQL Server 2000. Both ways can use a CTE or derived table

For performance, I've found the aggregate works better. However, it looks like SQL Server 2008 ranking functions run far better than on SQL Server 2005. I'm not using SQL Server 2008 day to day yet (large dinsoaur corporate) so can't comment.

There are 2 relevant SO Questions but I can't find them currently. One is a questions about high logical IO with ranking functions, another is testing of ranking in comments over SQL 2k5 vs 2k8. Sorry.

--aggregate + CTE
;WITH cOldest AS
(
    SELECT name, MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
)
SELECT
    T.*
FROM
    @Test T
    JOIN
    cOldest C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
    T.ID

--aggregate + derived table
SELECT
    T.*
FROM
    @Test T
    JOIN
    (
    SELECT name, MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
    ) C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
    T.ID

--ranking + CTE
;WITH cOldest AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test
)
SELECT
    C.*
FROM
    cOldest C
WHERE
    C.rnDOB = 1
ORDER BY
    C.ID

--ranking + derived table
SELECT
    C.*
FROM
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test) C
WHERE
    C.rnDOB = 1
ORDER BY
    C.ID

Sql-server – How to select rows which have max and min of count

This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions.

WITH s
     AS (SELECT MyGroup,
                Count(MyGroup)              AS [Count],
                MAX(Count(MyGroup)) OVER () AS [MaxMyGroup],
                MIN(Count(MyGroup)) OVER () AS [MinMyGroup]
         FROM   MyTable
         GROUP  BY MyGroup)
SELECT Agg,
       MyGroup,
       V.[Count]
FROM   s
CROSS APPLY (VALUES ( 'Max', CASE WHEN [Count] = [MaxMyGroup] THEN [Count] END),
                     ('Min', CASE WHEN [Count] = [MinMyGroup] THEN [Count] END))
             V(Agg, [Count])
WHERE V.[Count] IS NOT NULL

Best Answer

Related Solutions

Sql-server – How to get the MAX row

Sql-server – How to select rows which have max and min of count

Related Question