Calculate Group Result And Merge It With Details

Please consider this result (Report): State Value1 Value2 Value3 --------------------------------------------- State1 103 23% 3 State2

Solution 1:

I think you are looking as below, I have used ROLLUP function to achive functionality.

DECLARE @tblStates AS Table
(
    GroupName VARCHAR(10),
    StateName VARCHAR(50),
    Value1 INT,
    Value2 INT,
    Value3 INT
)   

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

;WITH T as
(
    SELECT
        GroupName,
        StateName,
        AVG(Value1) AS Value1,
        AVG(Value2) AS Value2,
        MIN(Value3) AS Value3
    FROM @tblStates
    GROUP BY ROLLUP(GroupName,StateName)        
)
SELECT 
    CASE ISNULL(StateName,'') WHEN '' THEN GroupName ELSE StateName END AS StateName,
    Value1,
    Value2,
    Value3      
FROM T  
WHERE 
T.GroupName IS NOT NULL 
ORDER BY GroupName

Output:

Solution 2:

I guess you are looking for the GROUPING SETS. This functionality allows you to perform aggregation in one statement grouping by different values in the same GROUP BY clause.

So, I guess you need something like this (first add in your data to which group it belongs):

Id      StateName      Value1       Value2      Value3  Group
1       State1           1             2          3     Group01
2       State2           4             2          2     Group01
3       State2           3             3          8     Group01
4       State4           3             8          5     Group02
...

Then, you will have:

SELECT ISNULL([Group],[StateName])
      ,AVG()
FROM ...
GROUP BY GROUPING SETS
(
    ([Group])
   ,([Group], [StateName])
);