SQL PARTITION BY ( ) Clause

This PARTITION BY ( ) Clause is used to partition the rows and group them into the table. The PARTITION BY ( ) Clause is useful when the query is performed in a particular group. This clause is inside the OVER ( ) clause with a particular group of rows. The groups of rows formed by the clause are known as WINDOW. The clause is worked by the Window function as RANK (), LEAD (), etc.

• Syntax for PARTITION BY ( ) Clause –

WINDOW_FUNCTION(column_name) OVER (PARTITION BY(column_name))

ORDER BY Clause can also be used with the PARTITION BY () clause.

DEMO TABLE –

Product Table

Example – Consider the above Product table of the Customer database and evaluate the Sum of Sales for each Category.

SELECT Category,Region,City,[Country/Region],[Customer Name], SUM(Sales) OVER (PARTITION BY Category ) AS Total_sales FROM [dbo].[Products];

The output after executing the above statement is as shown –

Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.