ROLLUP, Different rounding in grand total

Question

Related to: Calculate group result and merge it with details

The problem is I want different rounding precision in grand total. please consider this code:

DECLARE @tblStates AS Table
(
    StateName VARCHAR(50),
    Value1 DECIMAL(5,2),
    Value2 DECIMAL(5,2),
    Value3 DECIMAL(5,2)
)   

INSERT INTO @tblStates VALUES('State1',103,23,3)
INSERT INTO @tblStates VALUES('State2',105,32,12)
INSERT INTO @tblStates VALUES('State3',150,2,23)
INSERT INTO @tblStates VALUES('State4',50,10,8)
INSERT INTO @tblStates VALUES('State5',80,22,1)
INSERT INTO @tblStates VALUES('State6',20,18,45)

DECLARE @tblStatesGroups AS Table
(
    [Group] VARCHAR(50),
    [State] VARCHAR(50)
)   

INSERT INTO @tblStatesGroups VALUES('Group1','State1')
INSERT INTO @tblStatesGroups VALUES('Group1','State2')
INSERT INTO @tblStatesGroups VALUES('Group1','State3')
INSERT INTO @tblStatesGroups VALUES('Group2','State4')
INSERT INTO @tblStatesGroups VALUES('Group2','State5')
INSERT INTO @tblStatesGroups VALUES('Group2','State6')

;WITH T as
(
    SELECT
        groups.[Group],
        StateName,
        AVG(Value1) AS Value1,
        AVG(Value2) AS Value2,
        MIN(Value3) AS Value3
    FROM @tblStates states inner join @tblStatesGroups groups on states.StateName = groups.[State]
GROUP BY ROLLUP(groups.[Group],StateName)        
)
SELECT 
    CASE ISNULL(StateName,'') WHEN '' THEN [Group] ELSE StateName END AS StateName,
    Value1,
    Value2,
    Value3      
FROM T  
ORDER BY [Group]

I want sub totals have 1 decimal point precision and grand total has 2 decimal point precision. Is there any way?

Thanks

Answer 1

The only way is to set precision and convert values to strings so that column types would be same. But do you really need that?

SELECT 
    Value1,
    case 
        when StateName is null and [Group] is null then cast(cast(Value1 as decimal(20, 2)) as varchar(30)) 
        when StateName is null and [Group] is not null then cast(cast(Value1 as decimal(20, 1)) as varchar(30)) 
        else cast(cast(Value1 as decimal(20, 2)) as varchar(30)) 
    end 
FROM T  
ORDER BY [Group]