WINDOW Function
Introduction
The function named WINDOW belongs to 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 to deeper in WINDOW function we straight go to an Example to understand it in 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.