Over a million developers have joined DZone.

WINDOW Function

· Java Zone

Navigate the Maze of the End-User Experience and pick up this APM Essential guide, brought to you in partnership with CA Technologies

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.


Thrive in the application economy with an APM model that is strategic. Be E.P.I.C. with CA APM.  Brought to you in partnership with CA Technologies.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}