{"id":1707,"date":"2018-09-08T02:36:22","date_gmt":"2018-09-08T09:36:22","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1707"},"modified":"2025-02-09T20:11:57","modified_gmt":"2025-02-10T03:11:57","slug":"sql-window-functions","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/","title":{"rendered":"SQL Window Functions"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn about SQL window functions that solve complex query challenges easily.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQL Window Functions #<\/a><\/h2>\r\n\r\n\r\n\r\n

The aggregate functions<\/a> perform calculations across rows and return a single output row.<\/p>\r\n\r\n\r\n\r\n

The following query uses the SUM()<\/a><\/code> aggregate function to calculate the total salary of all employees in the company:<\/p>\r\n\r\n\r\n

SELECT<\/span>\r\n  SUM<\/span>(salary) total_salary\r\nFROM<\/span>\r\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 total_salary\r\n--------------\r\n    322400.00<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The output indicates that query group rows from the \u00a0employees<\/code> table\u00a0into a single row.<\/p>\r\n\r\n\r\n\r\n
Like an aggregate function, a window function operates on a set of rows. However, a window function does not group rows into a single row.<\/p>\r\n\r\n\r\n\r\n
For example, the following query uses the SUM()<\/code> function as a window function:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  first_name,\r\n  last_name,\r\n  salary,\r\n  SUM<\/span>(salary) OVER<\/span> () total_salary\r\nFROM<\/span>\r\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
It returns the salary of each individual employee along with the total salary of all employees:<\/p>\r\n\r\n\r\n
 first_name  |  last_name  |  salary  | sum_salary\r\n-------------+-------------+----------+------------\r\n Steven      | King        | 24000.00 |  322400.00\r\n Neena       | Kochhar     | 17000.00 |  322400.00\r\n Lex         | De Haan     | 17000.00 |  322400.00\r\n Alexander   | Hunold      |  9000.00 |  322400.00\r\n Bruce       | Ernst       |  6000.00 |  322400.00\r\n...<\/code><\/span><\/pre>\r\n\r\n\r\n
In this example, the OVER()<\/code> clause makes the SUM()<\/code> function a window function.<\/p>\r\n\r\n\r\n\r\n
The following picture illustrates the main difference between aggregate and window functions:<\/p>\r\n\r\n\r\n\r\n
\"sql<\/figure>\r\n\r\n\r\n\r\n
SQL window function syntax #<\/a><\/h2>\r\n\r\n\r\n\r\n
Here’s the basic syntax of a window function:<\/p>\r\n\r\n\r\n
window_function_name ( expression ) OVER (\r\n    partition_clause\r\n    order_clause\r\n    frame_clause\r\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
window_function_name<\/code><\/p>\r\n\r\n\r\n\r\n
expression<\/code><\/p>\r\n\r\n\r\n\r\n
The expression or column on which the window function operates.<\/p>\r\n\r\n\r\n\r\n
OVER<\/code> clause<\/p>\r\n\r\n\r\n\r\n
The OVER<\/code> clause defines a window or a partition. The OVER<\/code> clause consists of three clauses:<\/p>\r\n\r\n\r\n\r\n
The window function name, such as ROW_NUMBER()<\/a><\/code>, RANK()<\/a><\/code>, and SUM()<\/a><\/code>.<\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n