DZone
Java Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Java Zone > WINDOW Function

WINDOW Function

Joydeep Das user avatar by
Joydeep Das
·
Nov. 11, 14 · Java Zone · Interview
Like (0)
Save
Tweet
2.19K Views

Join the DZone community and get the full member experience.

Join For 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.


Database

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • APIs Outside, Events Inside
  • Real-Time Supply Chain With Apache Kafka in the Food and Retail Industry
  • How To Integrate Third-Party Login Systems in Your Web App Using OAuth 2.0
  • Evolving Domain-Specific Languages

Comments

Java Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo