T-SQL group by partition

I have below table in SQL server 2008.Please help to get expected output

Thanks.

CREATE TABLE [dbo].[Test]([Category] [varchar](10) NULL,[Value] [int] NULL,
[Weightage] [int] NULL,[Rn] [smallint] NULL ) ON [PRIMARY]

insert into Test values ('Cat1',310,674,1),('Cat1',783,318,2),('Cat1',310,96,3),('Cat1',109,917,4),('Cat2',441,397,1),('Cat2',637,725,2),('Cat2',460,742,3),('Cat2',542,583,4),('Cat2',601,162,5),('Cat2',45,719,6),('Cat2',46,305,7),('Cat3',477,286,1),('Cat3',702,484,2),('Cat3',797,836,3),('Cat3',541,890,4),('Cat3',750,962,5),('Cat3',254,407,6),('Cat3',136,585,7),('Cat3',198,477,8),('Cat4',375,198,1),('Cat4',528,351,2),('Cat4',845,380,3),('Cat4',7开发者_如何转开发16,131,4),('Cat4',781,919,5)

For per category Average Weightage

SELECT
   Category,
   AVG(Value),
   SUM(CASE WHEN RN<4 THEN Weightage ELSE 0 END) / (NULLIF(SUM(CASE WHEN RN<4 THEN 1 ELSE 0 END), 0))
FROM
   MyTable
GROUP BY
   Category

Average Weightage over the whole set

SELECT
   M.Category,
   AVG(Value),
   foo.AvgWeightage
FROM
   MyTable M
   CROSS JOIN
   (SELECT AVG(Weightage) As AvgWeightage FROM MyTable WHERE Rn < 4) foo
GROUP BY
   M.Category, foo.AvgWeightage

Simple:)

  SELECT Category,  
        AVG(Value) AS AvgValue, 
        AVG(CASE WHEN RN< 4 THEN (Weightage) END ) AS AvgWeightage
  FROM Test
  GROUP BY Category

Try this

SELECT AvgValue.Category, AvgValue.AvgValue, AvgWeight.Weight
FROM(
(SELECT c.Category, 
        AVG(c.Value) AS AvgValue
    FROM Test c
    GROUP BY Category) AvgValue
INNER JOIN 
(SELECT Category, AVG(Weightage) AS Weight
    FROM Test
    WHERE Rn < 4
    GROUP BY Category) AvgWeight
ON AvgValue.Category = AvgWeight.Category)