{"id":1793,"date":"2018-09-17T08:26:14","date_gmt":"2018-09-17T15:26:14","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1793"},"modified":"2025-02-08T05:20:00","modified_gmt":"2025-02-08T12:20:00","slug":"sql-partition-by","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-partition-by\/","title":{"rendered":"SQL PARTITION BY"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL PARTITION BY<\/code> clause to divide a result into multiple partitions on which a window function can operate.<\/p>\n\n\n\n

Introduction to the SQL PARTITION BY clause #<\/a><\/h2>\n\n\n\n

In SQL, a window function<\/a> allows you to calculate across table rows that are somehow related to the current row.<\/p>\n\n\n\n

A window function uses the OVER<\/code> clause to define the window or a set of rows on which a window function operates. The OVER<\/code> clause includes optional PARTITION BY<\/code> and ORDER BY<\/code> clause.<\/p>\n\n\n\n

Here’s the basic syntax of a window function that uses PARTITION BY<\/code> and ORDER BY<\/code> clauses:<\/p>\n\n\n

window_function (expression) OVER (\n  PARTITION BY\n    column1,\n    column2\n  ORDER BY\n    column3,\n    column4\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you omit the PARTION BY<\/code> clause, the window function treats the whole result set as a single partition.<\/p>\n\n\n\n
When you use the PARTITION BY<\/code> clause, it divides the result set into multiple partitions. The window function will operate on each partition independently.<\/p>\n\n\n\n
Suppose we have the following salary_reports<\/code> table:<\/p>\n\n\n\n
state<\/th>job<\/th>salary<\/th><\/tr><\/thead>
California<\/td>IT<\/td>150000.00<\/td><\/tr>
California<\/td>Marketing<\/td>130000.00<\/td><\/tr>
Texas<\/td>IT<\/td>100000.00<\/td><\/tr>
Texas<\/td>Marketing<\/td>80000.00<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
SQL script for creating the salary_reports table<\/summary>
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> salary_reports;\n\nCREATE<\/span> TABLE<\/span> salary_reports (\n  state varchar<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  job varchar<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  salary DEC<\/span>(11<\/span>, 2<\/span>)\n);\n\nINSERT<\/span> INTO<\/span>\n  salary_reports (state, job, salary)\nVALUES<\/span>\n  ('California'<\/span>, 'IT'<\/span>, 150000<\/span>),\n  ('California'<\/span>, 'Marketing'<\/span>, 130000<\/span>),\n  ('Texas'<\/span>, 'IT'<\/span>, 100000<\/span>),\n  ('Texas'<\/span>, 'Marketing'<\/span>, 80000<\/span>);\n\nSELECT<\/span>\n  *\nFROM<\/span>\n  salary_reports;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
The following query uses the AVG()<\/code> window function to calculate the average salary across states and jobs:<\/p>\n\n\n
SELECT<\/span>\n  state,\n  job,\n  salary,\n  ROUND<\/span>(AVG<\/span>(salary) OVER<\/span> (), 2<\/span>) average_salary\nFROM<\/span>\n  salary_reports\nORDER<\/span> BY<\/span>\n  state,\n  job;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Output:<\/p>\n\n\n
   state    |    job    |  salary   | average_salary\n------------+-----------+-----------+----------------<\/span>\n California | IT        | 150000.00 |      115000.00\n California | Marketing | 130000.00 |      115000.00\n Texas      | IT        | 100000.00 |      115000.00\n Texas      | Marketing |  80000.00 |      115000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the AVG<\/code> function calculates the average salary of all salaries in four rows:<\/p>\n\n\n
(150,000 + 130,000 + 100,000 + 80,000) \/ 4<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Partitioning data by jobs #<\/a><\/h2>\n\n\n\n
To calculate average salaries by jobs across states, you can use the PARITION BY<\/code> clause to divide the result set into partitions:<\/p>\n\n\n
SELECT<\/span>\n  state,\n  job,\n  salary,\n  ROUND<\/span>(AVG<\/span>(salary) OVER<\/span> (PARTITION<\/span> BY<\/span> job), 2<\/span>) average_salary\nFROM<\/span>\n  salary_reports\nORDER<\/span> BY<\/span>\n  state,\n  job;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   state    |    job    |  salary   | average_salary\n------------+-----------+-----------+----------------<\/span>\n California | IT        | 150000.00 |      125000.00\n California | Marketing | 130000.00 |      105000.00\n Texas      | IT        | 100000.00 |      125000.00\n Texas      | Marketing |  80000.00 |      105000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the PARITION BY<\/code> clause divides the result set by the values in the job columns. Since we have two jobs (IT<\/code> & Marketing<\/code>), the PARTITION BY<\/code> clause divides the result set into two partitions:<\/p>\n\n\n\n
The first partition:<\/p>\n\n\n
   state    |    job    |  salary\n------------+-----------+-----------<\/span>\n California | IT        | 150000.00\n Texas      | IT        | 100000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The second partition:<\/p>\n\n\n
   state    |    job    |  salary\n------------+-----------+-----------<\/span>\n California | Marketing | 130000.00\n Texas      | Marketing |  80000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AVG()<\/code> window function calculates the average salary of each partition and returns 125,000<\/code> for the first partition:<\/p>\n\n\n
(150,000 + 100,000) \/ 2 = 125,000<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And 105,000<\/code> for the second partition:<\/p>\n\n\n
(100,000 + 80,000) \/2 = 105,000<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Partitioning data by states #<\/a><\/h2>\n\n\n\n
The following query uses the PARTITION BY<\/code> clause to calculate the average salaries by states across jobs:<\/p>\n\n\n
SELECT<\/span>\n  state,\n  job,\n  salary,\n  ROUND<\/span>(AVG<\/span>(salary) OVER<\/span> (PARTITION<\/span> BY<\/span> state), 2<\/span>) average_salary\nFROM<\/span>\n  salary_reports\nORDER<\/span> BY<\/span>\n  state,\n  job;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   state    |    job    |  salary   | average_salary\n------------+-----------+-----------+----------------<\/span>\n California | IT        | 150000.00 |      140000.00\n California | Marketing | 130000.00 |      140000.00\n Texas      | IT        | 100000.00 |       90000.00\n Texas      | Marketing |  80000.00 |       90000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the PARITTION BY<\/code> clause divides the result set by the values in the state<\/code> column. <\/p>\n\n\n\n
Since there are two states California<\/code> and Texas<\/code>, the PARTITION BY<\/code> clause divides the result set into two partitions, each per state:<\/p>\n\n\n\n
First partition:<\/p>\n\n\n
   state    |    job    |  salary\n------------+-----------+-----------<\/span>\n California | IT        | 150000.00\n California | Marketing | 130000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second partition:<\/p>\n\n\n
   state    |    job    |  salary   | average_salary\n------------+-----------+-----------+----------------<\/span>\n Texas      | IT        | 100000.00 |       90000.00\n Texas      | Marketing |  80000.00 |       90000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AVG()<\/code> window function calculates the average salary for each partition. It returns 140,000<\/code> for the first partition and 90,000<\/code> for the second one.<\/p>\n\n\n\n
Partitioning data by departments #<\/a><\/h2>\n\n\n\n
We’ll use the employees<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following query uses the SUM<\/code> window function with a PARTITION BY<\/code> clause to retrieve the employee salary along with the total salary of the current employee’s department:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  department_name,\n  salary,\n  SUM<\/span>(salary) OVER<\/span> (\n    PARTITION<\/span> BY<\/span> department_name\n  ) department_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\nORDER<\/span> BY<\/span>\n  department_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  | department_name  |  salary  | department_salary\n-------------+-------------+------------------+----------+-------------------<\/span>\n William     | Gietz       | Accounting       |  8300.00 |          20300.00\n Shelley     | Higgins     | Accounting       | 12000.00 |          20300.00\n Jennifer    | Whalen      | Administration   |  4400.00 |           4400.00\n Steven      | King        | Executive        | 24000.00 |          58000.00\n Neena       | Kochhar     | Executive        | 17000.00 |          58000.00\n Lex         | De Haan     | Executive        | 17000.00 |          58000.00\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
How the query works:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Note that the query uses the ROUND()<\/a><\/code> function to round the average values to numbers with two decimal places.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n