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

WINDOW Function

DZone's Guide to

WINDOW Function

· Java Zone
Free Resource

Bitbucket is for the code that takes us to Mars, decodes the human genome, or drives your next car. What will your code do? Get started with Bitbucket today, it's free.

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.


Bitbucket is the Git solution for professional teams who code with a purpose, not just as a hobby. Get started today, it's free.

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}