WINDOW Function
Join the DZone community and get the full member experience.
Join For FreeIntroduction
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.
Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Trending
-
IDE Changing as Fast as Cloud Native
-
Future of Software Development: Generative AI Augmenting Roles and Unlocking Co-Innovation
-
Understanding the Role of ERP Systems in Modern Software Development
-
DevOps vs. DevSecOps: The Debate
Comments