WINDOW Function

Introduction

The function named WINDOW belongs to the SET Function and it applies to the set of rows. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012. Without going too deep into the WINDOW function, we go straight to an Example to understand it in a better way.

Step – 1 [ Create a Base Table ]

IF OBJECT_ID(N'dbo.tbl_EXAMPLETABLE', N'U')IS NOT NULL
  BEGIN
  DROP TABLE [dbo].[tbl_EXAMPLETABLE];
  END
GO
CREATE TABLE [dbo].[tbl_EXAMPLETABLE]
  (
    IDNO  INT  NOT NULL,
    VALUE  INT  NOT NULL
  );

Step-2 [ Inserting Some Records in Base Table ]

INSERT INTO  [dbo].[tbl_EXAMPLETABLE]
  (IDNO,VALUE)
VALUES (1, 10),(1, 20),(1, 30),
  (2, 20),(2, 30),(2, 40);
SELECT IDNO, VALUE FROM [dbo].[tbl_EXAMPLETABLE];

IDNO            VALUE

-----------     -----------

1               10

1               20

1               30

2               20

2               30

2               40

(6 row(s) affected)

Step-3 [ Now try to Group by ]

SELECT  IDNO,
  SUM(VALUE) AS [SUM VALUE],
         AVG(VALUE) AS [AVG VALUE]
FROM  [dbo].[tbl_EXAMPLETABLE]
GROUP BY IDNO;

IDNO        SUM VALUE   AVG VALUE

----------- ----------- -----------

1           60          20

2           90          30

(2 row(s) affected)

Step – 4 [ Now we want the Output Like this ]

IDNO    VALUE    SUM VALUE       AVG VALUE

------- -------- --------------- -----------

1       10       60              20

1       20       60              20

1       30       60              20

2       20       90              30

2       30       90              30

2       40       90              30

Step – 5 [ Is it Possible Before SQL 2012 ]

Yes it is but takes some efforts.

SELECT  a.IDNO,
  a.VALUE,
    b.[SUM VALUE],
    b.[AVG VALUE]
FROM  [dbo].[tbl_EXAMPLETABLE] As a
  INNER JOIN
   (SELECT  IDNO,
    SUM(VALUE) AS [SUM VALUE],
  AVG(VALUE) AS [AVG VALUE]
    FROM  [dbo].[tbl_EXAMPLETABLE]
    GROUP BY IDNO)AS b ON a.IDNO = b.IDNO;    

IDNO    VALUE    SUM VALUE       AVG VALUE

------- -------- --------------- -----------

1       10       60              20

1       20       60              20

1       30       60              20

2       20       90              30

2       30       90              30

2       40       90              30

(6 row(s) affected)

Step – 6 [  What’s makes Easy in WINDOW function in SQL 2012 ]

We Just use the OVER() Clause

SELECT  IDNO,
  VALUE,
  SUM(VALUE) OVER() AS [SUM VALUE],
  AVG(VALUE) OVER() AS [AVG VALUE]
FROM  [dbo].[tbl_EXAMPLETABLE];

IDNO    VALUE    SUM VALUE       AVG VALUE

------- -------- --------------- -----------

1       10       150             25

1       20       150             25

1       30       150             25

2       20       150             25

2       30       150             25

2       40       150             25

(6 row(s) affected)

Step-7 [ We can use Partition By clause within OVER() ]

SELECT  IDNO,
  VALUE,
  SUM(VALUE) OVER(PARTITION BY IDNO) AS [SUM VALUE],
  AVG(VALUE) OVER(PARTITION BY IDNO) AS [AVG VALUE]
FROM  [dbo].[tbl_EXAMPLETABLE];

IDNO    VALUE    SUM VALUE       AVG VALUE

------- -------- --------------- -----------

1       10       60              20

1       20       60              20

1       30       60              20

2       20       90              30

2       30       90              30

2       40       90              30

(6 row(s) affected)

Hope you like it.