Calculate group result and merge it with details

问题:

Please consider this result (Report):

State       Value1       Value2        Value3
---------------------------------------------
State1        103         23%            3
State2        105         32%            12
State3        150         2%             23
Group1        120         19%            3
===================
State4        200         40%            5
State5        250         2%             12
Group2        225         21%            8
===================
...

I have groups and each group contains some states. No I want to run a query on my details data and run some custom function on each group to obtain Value1 to Value3. For simplicity consider AVG function.

I'm using Group By clause to generate states result but how I can merge group of states with my result?

Thanks

Edit 1)

this is the basic Data and this may not match above output:

Id      StateName      Value1       Value2      Value3
1       State1           1             2          3
2       State2           4             2          2
3       State2           3             3          8
4       State2           3             8          5
...

---

Edit 2)

For Example :

State1, State2, State3 Belong to `Group1`
State4, state5 Belong to `Group2`
...

回答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:

回答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])
);