WINDOW Function

Introduction

The function named WINDOW belongs to 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 to deeper in WINDOW function we straight go to an Example to understand it in 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.

Posted by: MR. JOYDEEP DAS