CODEWITHSUNDEEP
sql-servergroup-by
BrianMichaels
BrianMichaels

Reputation: 522

SELECT ROW based on MAX quantity and SUM quantity of grouped by fields

I am trying to SUM the qty rows and group them by part number and bin then choose the bin that had the max quantity to begin with. In the query below it is only choosing bin 1-B. My result set should be for Part 1-2345: Bin 1-A, SUM qty of bins = 150 , total in that bin = 100

CREATE TABLE inventory (
ID int IDENTITY(1,1) PRIMARY KEY,
bin nvarchar(25),
partnumber nvarchar(25),
qty int
);

INSERT INTO inventory ( bin, partnumber, qty)
VALUES ('1-A', '1-2345', '100'), ('1-A', '1-2347', '10'), ('1-A', '1-2348', 
'15'), ('1-B', '1-2345', '50'), ('1-B', '1-2347', '50'), ('1-B', '1-2348', 
'55')

;With cte as
    ( SELECT bin, partnumber, sum(qty) qty
    , ROW_NUMBER() OVER( Partition By  partnumber ORDER BY bin desc) as rn 
from inventory
     GROUP BY bin, partnumber) 
SELECT * FROM cte where rn = 1

Result set should be
Output:

bin partnumber  sum_of_bins max_qty_in_bin  
1-A 1-2345      150         100             
1-B 1-2347      60          50              
1-B 1-2348      70          55

Upvotes: 1

Views: 53

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23837

Since you didn't give us sample output, it is not clear what you are trying to do. From your last sentence:

With cte as
( SELECT bin, partnumber, 
      sum(qty) over (Partition By  partnumber) as sumQty,
      sum(qty) over (Partition By  partnumber Order by bin) as totQty,
     ROW_NUMBER() OVER ( Partition By  partnumber ORDER BY bin) as rn 
from inventory
) 
SELECT * FROM cte where rn = 1;

Here is DBFiddle Demo.

Upvotes: 1

Dave C
Dave C

Reputation: 7402

Give this a whirl:

DECLARE @inventory TABLE (ID int IDENTITY(1,1), bin nvarchar(25), partnumber nvarchar(25), qty int);

INSERT INTO @inventory ( bin, partnumber, qty)
VALUES ('1-A', '1-2345', '100'), ('1-A', '1-2347', '10'), ('1-A', '1-2348','15'), ('1-B', '1-2345', '50'), ('1-B', '1-2347', '50'), ('1-B', '1-2348', '55')

;WITH CTE AS
    ( 
        SELECT bin, partnumber
                , sum(qty) OVER(Partition By partnumber) AS sum_of_bins
                , max(qty) OVER(Partition By partnumber) AS max_qty_in_bin
                , ROW_NUMBER() OVER(Partition By partnumber ORDER BY qty desc) as rn 
        FROM @inventory
        GROUP BY bin, partnumber, qty) 

SELECT * 
FROM cte
WHERE rn=1

Output:

bin partnumber  sum_of_bins max_qty_in_bin  rn
1-A 1-2345      150         100             1
1-B 1-2347      60          50              1
1-B 1-2348      70          55              1

Upvotes: 1

Related Questions