CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Ivan-Mark Debono
Ivan-Mark Debono

Reputation: 16310

How to use ROW_NUMBER when grouping records?

I have the following:

DECLARE @items TABLE
(
    ItemId int NOT NULL,
    [Description] varchar(255) NOT NULL,
    Amount money NOT NULL
);

INSERT INTO @items SELECT 1, 'A', 10;
INSERT INTO @items SELECT 2, 'A', 10;
INSERT INTO @items SELECT 3, 'B', 11;
INSERT INTO @items SELECT 4, 'B', 11;
INSERT INTO @items SELECT 5, 'B', 11;
INSERT INTO @items SELECT 6, 'C', 12;
INSERT INTO @items SELECT 7, 'C', 12;
INSERT INTO @items SELECT 8, 'A', 10;
INSERT INTO @items SELECT 9, 'A', 10;

SELECT 
    ROW_NUMBER() OVER(PARTITION BY b.ItemId ORDER BY b.[Description]),
    [Description],  
    COUNT(ItemId) OVER(PARTITION BY b.ItemId),
    SUM(Amount) OVER(PARTITION BY b.ItemId)
FROM @items b

The result should be:

1, A, 4, 40
2, B, 3, 33
3, C, 2, 24

However the items are not being grouped.

So how to I need to use ROW_NUMBER to group records?

Upvotes: 1

Views: 62

Answers (2)

Yuriy Tsarkov
Yuriy Tsarkov

Reputation: 2548

If you don't want use GROUP BY by itself you may do a subquery with two row_number(), something like this:

select ROW_NUMBER() over(order by t.[Description]), t.Description, t.cnt, t.summ
from (
SELECT 
    ROW_NUMBER() OVER(PARTITION BY b.[Description] ORDER BY b.[Description] )  rn,
    [Description],  
    COUNT(ItemId) OVER(PARTITION BY b.[Description]) cnt,
    SUM(Amount) OVER(PARTITION BY b.[Description]) summ
FROM @items b
) t where rn = 1

And anyway you shouldn't group data by the ItemId - it's a wrong way to achieve your aim

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Is this what you want?

SELECT ROW_NUMBER() OVER (ORDER BY i.Description),
       i.Description,  
       COUNT(*),
       SUM(i.Amount) 
FROM @items i
GROUP BY Description
ORDER BY Description;

Here is a rextester.

Upvotes: 2

Related Questions