March 6, 2012 at 1:37 pm
Hi all,
I need help with a ROW_NUMBER and a group by aggregate. It seems that I can not use the ROW_NUMBER function to group by the resulting set.
I need to sum the past n quaters of sales. The table has a field for the year and the quarter. In creating a view for this purpose, I tried the following code. The problem with this code is that it does not sum the rows that were ordered by the Sales set.
WITH SALES
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY [YearId] ORDER BY [Quarter]) as Row,
*,
ROW_NUMBER() OVER (ORDER BY [YearId]) RowId
FROM TEST
)
SELECT SUM(TotalSales)
FROM SALES
where RowId > (SELECT MAX(RowId) from SALES) - 2;
Here is a test case. The information was inserted into the table in a random way.
CREATE TABLE TEST( ID INT IDENTITY(1,1),
[Quarter] INT NOT NULL,
[YearId] INT NOT NULL,
[TotalSales] decimal (18,4)
CONSTRAINT [PK_BASE_1] PRIMARY KEY CLUSTERED
(
[Quarter] ASC,
[YearId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO TEST ([Quarter],[YearId], [TotalSales])
VALUES (4, 2010, NULL),
(3, 2010, 1000),
(3, 2011, 1000),
(1, 2011, 1000),
(2, 2011, NULL),
(4, 2011, 1000),
(2, 2010, 1000)
This code shows the sum to be only a 1000.00
WITH SALES
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY [YearId] ORDER BY [Quarter]) as Row,
*,
ROW_NUMBER() OVER (ORDER BY [YearId]) RowId
FROM TEST
)
SELECT SUM(TotalSales)
FROM SALES
where RowId > (SELECT MAX(RowId) from SALES) - 2;
While this code, shows the bottom two rows has a value of 1000.00
WITH SALES
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY [YearId] ORDER BY [Quarter]) as Row,
*,
ROW_NUMBER() OVER (ORDER BY [YearId]) RowId
FROM TEST
)
SELECT *
FROM SALES
where RowId > (SELECT MAX(RowId) from SALES) - 2;
This is the view of the entire table.
SELECT * FROM TEST
Is this behavior known? How can I acomplish the sum without resorting to a temp table.
Thank you.
March 6, 2012 at 1:49 pm
I have no idea why your query doesn't work, but if you re-write it as follows, it seems to work fine.
WITH SALES
AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY [YearId] ORDER BY [Quarter]) as Row,
*,
ROW_NUMBER() OVER (ORDER BY [YearId] DESC, [Quarter] DESC) RowId
FROM TEST
)
SELECT SUM( TotalSales )
FROM SALES
WHERE RowId <= 2
March 7, 2012 at 1:56 am
Recurs1on (3/6/2012)
I have no idea why your query doesn't work, but if you re-write it as follows, it seems to work fine.
WITH SALES
AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY [YearId] ORDER BY [Quarter]) as Row,
*,
ROW_NUMBER() OVER (ORDER BY [YearId] DESC, [Quarter] DESC) RowId
FROM TEST
)
SELECT SUM( TotalSales )
FROM SALES
WHERE RowId <= 2
The reason it doesn't return your expected sum of 2000 is that ROW_NUMBER() OVER (ORDER BY [YearId] DESC) RowID only specifies 'ORDER BY [YearID]', as you've identified correctly. There are 4 rows in the test set that all have the same highest yearID of 2011. You requested the sum of only the 'last 2 rows' of those 4. One of these 4 rows has a TotalSales value of NULL. Since you've not specified how to order the rows if they have the same value of YearID, SQL 'accidently' chose a plan in which the rows were ordered such that the row with the null value was among those last 2. hence 1000 + null = 1000.
This is why it warns you with the message:
"Warning: Null value is eliminated by an aggregate or other SET operation."
March 7, 2012 at 6:30 am
Thank you R.P.Rozema.
That makes sense, and it does work.
Great!!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply