with rollup and null values

Question

with rollup and null values

naidzionik

SSC-Addicted

Points: 459
More actions
October 16, 2015 at 6:46 am

#309008

I have script that creates table and inserts values.
CREATE TABLE [dbo].[TestRollup]
(
[Dim1] [nvarchar](255) NULL,
[Dim2] [nvarchar](255) NULL,
[Dim3] [nvarchar](255) NULL,
[Metric] [float] NULL
)
INSERT INTO [dbo].[TestRollup]
([Dim1],[Dim2],[Dim3],[Metric])
VALUES
('TestDim1','Dim2Value1','Dim3Value1',1),
('TestDim1','Dim2Value1','Dim3Value2',3),
('TestDim1','Dim2Value2','Dim3Value1',7),
('TestDim1','Dim2Value2','Dim3Value3',5),
('TestDim1','Dim2Value3','Dim3Value2',NULL),
('TestDim1','Dim2Value3','Dim3Value2',NULL),
('TestDim1','Dim2Value3','Dim3Value5',2),
('TestDim1','Dim2Value3','Dim3Value5',1)
The following query is executed correctly.
SELECT
[Dim1]
,[Dim2]
,[Dim3]
,SUM([Metric]) Sum_Metric
,GROUPING(Dim1) Grouping_Dim1
,GROUPING(Dim2) Grouping_Dim2
,GROUPING(Dim3) Grouping_Dim3
FROM [dbo].[TestRollup]
GROUP BY [Dim1],[Dim2],[Dim3]
WITH ROLLUP
but if I use linked server Sum_Metric column is not calculated correctly for subtotals.
SELECT
[Dim1]
,[Dim2]
,[Dim3]
,SUM([Metric]) Sum_Metric
,GROUPING(Dim1) Grouping_Dim1
,GROUPING(Dim2) Grouping_Dim2
,GROUPING(Dim3) Grouping_Dim3
FROM <linked_server>.<name_db>.[dbo].[TestRollup]
GROUP BY [Dim1],[Dim2],[Dim3]
WITH ROLLUP
Result:
Dim1Dim2Dim3Sum_MetricGrouping_Dim1Grouping_Dim2Grouping_Dim3
----------------------------------------------------------------------------------------------
TestDim1Dim2Value1Dim3Value11000
TestDim1Dim2Value1Dim3Value23000
TestDim1Dim2Value1NULL4001
TestDim1Dim2Value2Dim3Value17000
TestDim1Dim2Value2Dim3Value35000
TestDim1Dim2Value2NULL12001
TestDim1Dim2Value3Dim3Value2NULL000
TestDim1Dim2Value3Dim3Value53000
TestDim1Dim2Value3NULLNULL001
TestDim1NULLNULLNULL011
NULLNULLNULLNULL111
Why is this happening? Any ideas? Thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Dean Mincic Mr or Mrs. 500 Points: 550 More actions · Answer 1

Hi naidzionik,

It might have something to do with the linked server provider (not sure) and how SUM handles NULLs (should just ignore it)

eg. this worked for me:

SELECT

[Dim1]

,[Dim2]

,[Dim3]

,SUM(ISNULL([Metric],0)) Sum_Metric

,GROUPING(Dim1) Grouping_Dim1

,GROUPING(Dim2) Grouping_Dim2

,GROUPING(Dim3) Grouping_Dim3

FROM <linked_server>.<name_db>.[dbo].[TestRollup]

GROUP BY [Dim1],[Dim2],[Dim3]

WITH ROLLUP

Dim1Dim2Dim3Sum_MetricGrouping_Dim1Grouping_Dim2Grouping_Dim3

----------------------------------------------------------------------------------------------

TestDim1Dim2Value1Dim3Value11000

TestDim1Dim2Value1Dim3Value23000

TestDim1Dim2Value1NULL4001

TestDim1Dim2Value2Dim3Value17000

TestDim1Dim2Value2Dim3Value35000

TestDim1Dim2Value2NULL12001

TestDim1Dim2Value3Dim3Value20 000

TestDim1Dim2Value3Dim3Value53000

TestDim1Dim2Value3NULL3 001

TestDim1NULLNULL19 011

NULLNULLNULL19 111

.. and it worked without GROUPING() function