SQL Server Find Max and Min in Group

Andi Rahman 0
Viewed 39

I'm using Microsoft SQL Server and have a table like this:

DATE ITEM BUYER QTY_BUY
2022-01-01 ITEM A TOMMY 5
2022-01-01 ITEM A BENNY 3
2022-01-01 ITEM A ANDY 1
2022-01-01 ITEM A JOHN 8
2022-01-01 ITEM B TOMMY 2
2022-01-01 ITEM B BENNY 10
2022-01-01 ITEM B ANDY 3
2022-01-01 ITEM B JOHN 6
2022-01-02 ITEM A TOMMY 3
2022-01-02 ITEM A BENNY 0
2022-01-02 ITEM A ANDY 5
2022-01-02 ITEM A JOHN 6

I want to show top buyer and min buyer group by date and item, so it will look like:

DATE ITEM TOP_BUYER TOP_QTY MIN_BUYER QTY_MIN
2022-01-01 ITEM A JOHN 8 ANDY 1
2022-01-01 ITEM B BENNY 10 TOMMY 2
2022-01-02 ITEM A JOHN 6 BENNY 0

Please help me to do that, I try so many trick but cannot reach it. Thanks in advance

group-by sql-server sql
3 Answers

We can handle this requirement using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY) rn1,
              ROW_NUMBER() OVER (PARTITION BY DATE, ITEM ORDER BY QTY_BUY DESC) rn2 
    FROM yourTable
)

SELECT DATE, ITEM,
       MAX(CASE WHEN rn2 = 1 THEN BUYER END) AS TOP_BUYER,
       MAX(CASE WHEN rn2 = 1 THEN QTY_BUY END) AS TOP_QTY,
       MAX(CASE WHEN rn1 = 1 THEN BUYER END) AS MIN_BUYER,
       MAX(CASE WHEN rn1 = 1 THEN QTY_BUY END) AS QTY_MIN
FROM cte
GROUP BY DATE, ITEM
ORDER BY DATE, ITEM;
Tim Biegeleisen0

The solution is to use first_value + partition over

This query was tested inside SQL Server

select distinct [date], Item
    , FIRST_VALUE(buyer) OVER (partition by [date], item ORDER BY qty_buy desc) AS Top_Buyer
    , FIRST_VALUE(qty_buy) OVER (partition by [date], item ORDER BY qty_buy desc)  AS Top_Qty
    , FIRST_VALUE(buyer) OVER (partition by [date], item ORDER BY [date], item, qty_buy asc) AS Min_Buyer
    , FIRST_VALUE(qty_buy) OVER (partition by [date], item ORDER BY [date], item, qty_buy asc) AS Qty_Min
from testtable
Frederik van Lierde0

It can also be done using simple group by and outer apply,
see this dbfiddle

select t.bdate,
       t.item,
       max(tb.buyer) as top_buyer,
       max(t.qty) as top_qty,
       max(mb.buyer) as min_buyer,
       min(t.qty) as qty_min
  
from   test t
  outer apply ( select  top 1 t2.buyer
                from   test t2
                where  t2.bdate = t.bdate
                and    t2.item = t.item
                order by t2.qty desc
              ) tb
  outer apply ( select  top 1 t2.buyer
                from   test t2
                where  t2.bdate = t.bdate
                and    t2.item = t.item
                order by t2.qty
              ) mb
  
group by t.bdate,
       t.item
order by t.bdate
GuidoG0
Related