TSQL Select Min & Max row when grouping

Question

Lets say I have a table containing many many rows like this:

ID        Range         Range_begining        Profit
----------------------------------------------------
 1    (100-150)                    100           -20
 2    (200-250)                    200          40.2
 3    (100-150)                    100           100
 4    (450-500)                    450           -90
 ...

I'm doing a simple query like this:

SELECT max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
    FROM
        Orders
    GROUP BY
        Range_begining

After this query is run I get results like this:

Range        Count        AVG Profit
------------------------------------
(100-150)        2                40
(200-250)        1              40.2
(450-500)        1               -90
 ...

Quite simple :)

What I need to do now is to select row with minimum and maximum profit where count is bigger than 10 (this is a parameter)

I was able to get minimum value with this:

SELECT TOP 1 [Range], [AVG Profit] FROM (
     SELECT max([Range]) AS 'Range'
         , count(ID) AS 'Count'
         , round(avg([Profit]), 2) AS 'AVG Profit'
        FROM
            Orders
        GROUP BY
            Range_begining) X
WHERE
    [Count]>10
ORDER BY 
    [AVG Profit] ASC --or DESC if I want max profit

I was thinking of doing an UNION for above query with ORDER BY DESC, but it isn't the best solution.

What I need to do:
Select 2 rows: one with minimum, second with maximum AVG Profit when grouping by Range.

EDIT: If I add 2 move columns to my main data table like this:

ID        Range         Range_begining        Profit        OrderDate     Company
---------------------------------------------------------------------------------
 1    (100-150)                    100           -20        2012-01-02          1
 2    (200-250)                    200          40.2        2012-03-22          0
 3    (100-150)                    100           100        2012-02-05          0
 4    (450-500)                    450           -90        2012-05-12          1
 ...

And then try to add 2 more conditions like this:

; with ordering as (
  SELECT max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
     , row_number() over (order by avg([Profit])) rn_min
     , row_number() over (order by avg([Profit]) desc) rn_max
    FROM
        Orders
    GROUP BY
        Range_begining
    HAVING COUNT(ID) > 10
    AND [Company]=@company
    AND (@from= '' OR [OrderDate]>=@from)
    AND (@to= '' OR [OrderDate]<=@to)
)
select [range], [count], [avg profit]
  from ordering
 where (rn_max = 1 or rn_min = 1)

I get an error because [Company] and [OrderDate]

is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

How can I fix this?

EDIT2 Got it working!

; with ordering as (
  SELECT max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
     , row_number() over (order by avg([Profit])) rn_min
     , row_number() over (order by avg([Profit]) desc) rn_max
    FROM
        Orders
    WHERE
    [Company]=@company
    AND (@from= '' OR [OrderDate]>=@from)
    AND (@to= '' OR [OrderDate]<=@to)
    GROUP BY
        Range_begining
    HAVING COUNT(ID) > 10
)
select [range], [count], [avg profit]
  from ordering
 where (rn_max = 1 or rn_min = 1)

EDIT 3 Can I return another column with description like this:

Range        AVG Profit               Description
-------------------------------------------------
(200-250)          40.2           Max profit here
(450-500)           -90     Min profit, well done

EDIT 4 Fast answer (based on @Nikola Markovinović answer):

; with ordering as (
  SELECT max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
     , row_number() over (order by avg([Profit])) rn_min
     , row_number() over (order by avg([Profit]) desc) rn_max
    FROM
        Orders
    WHERE
    [Company]=@company
    AND (@from= '' OR [OrderDate]>=@from)
    AND (@to= '' OR [OrderDate]<=@to)
    GROUP BY
        Range_begining
    HAVING COUNT(ID) > 10
)
    SELECT
    CASE WHEN rn_max=1 THEN 'This is max' ELSE 'Min' END AS 'Description'
    ,[range]
    ,[count]
    ,[avg profit]
    FROM ordering
    WHERE (rn_max = 1 or rn_min = 1)

Solution 1

You might do it at once using window functions:

; with ordering as (
  SELECT max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
     , row_number() over (order by avg([Profit])) rn_min
     , row_number() over (order by avg([Profit]) desc) rn_max
    FROM
        Orders
    GROUP BY
        Range_begining
    HAVING COUNT(ID) > 10
)
select [range], [count], [avg profit],
       case when rn_max = 1 
            then 'Max profit'
            else 'Min profit'
        end Description
  from ordering
 where (rn_max = 1 or rn_min = 1)

And here is Sql Fiddle example.

Solution 2

Here is a SQLFiddle example. In your last question query you can use HAVING instead of the nested query:

select * from
(SELECT TOP 1
    max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
    FROM
        Orders
    GROUP BY
        Range_begining
    having count(id)>10
    order by round(avg([Profit]), 2) ASC
) a
union all
select * from 
(
SELECT TOP 1
    max([Range]) AS 'Range'
     , count(ID) AS 'Count'
     , round(avg([Profit]), 2) AS 'AVG Profit'
    FROM
        Orders
    GROUP BY
        Range_begining
    having count(id)>10
    order by round(avg([Profit]), 2) desc
)b

Comments

[Misiu]

Thanks for such a fast answer :) I am wondering it this will be fast. As You can see top and bottom part is almost the same (asc/desc is changing) so can this be done ones? One option that come to my mind is to select grouped data into temporary table and then select min & max row. Maybe this will be faster, besides in You (and mine) solution we have to group data 2 times. If I have 100 rows that,s ok, but if I will have 10KK (10 millions) this will be a bit long.

[Nikola Markovinović]

@Misiu I just did some (limited) testing and in my case (700000 invoices, grouped by customer, averaging invoice value) both approaches work fast, but row_number() is almost twice as fast as union all.

[Misiu]

I'm testing it to. It looks like Your solution is much much faster, grouping is done just once. I would like to know SQL as You do ;)

[Misiu]

I've added 2 more conditions to my query, but I get error. Could You look on that? :)

[Nikola Markovinović]

@Misiu These conditions belong to where clause. Having is used to filter rows after aggregation.

[Misiu]

I've figured it out just a second ago. Silly me :)

[Misiu]

One more question - Can I add another column 'DESCRIPTION' that will say 'MAX PROFIT' for max value and 'MIN PROFIT' for min. I'll edit my question in just a second.

[Nikola Markovinović]

@Misiu Please check case when ... end section of my answer.