T-SQL group by partition

Question

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

alt text

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',716,131,4),('Cat4',781,919,5)

When you say "average per weightage for rn<4", is that per category too or over the whole set? — gbn, Jan 04 '11 at 19:41
I answered for both cases anyway... And +1 for one of the best phrased questions by using a picture (and with scripts too!) I've seen here. — gbn, Jan 04 '11 at 19:45

score 1 · Accepted Answer · answered Jan 04 '11 at 19:41

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

score 1 · Answer 2 · answered Aug 27 '11 at 14:03

1

Simple:)

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

answered Aug 27 '11 at 14:03

Alfred Yomtov

11
1

Simon · Answer 3 · 2011-01-04T20:07:02.780

0

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)

edited Jan 04 '11 at 20:07

answered Jan 04 '11 at 19:46

Simon

413
5
14

Your first derived table will fail because of w.Weight – gbn Jan 04 '11 at 19:50
Sorry, my error, thought I'd removed that as I was playing with it. Should be better now – Simon Jan 04 '11 at 20:06

T-SQL group by partition

3 Answers3