Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

WINDOW Function

DZone's Guide to

WINDOW Function

· Java Zone ·
Free Resource

FlexNet Code Aware, a free scan tool for developers. Scan Java, NuGet, and NPM packages for open source security and open source license compliance issues.

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.


 Scan Java, NuGet, and NPM packages for open source security and license compliance issues. 

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}