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

Category Region City Country Sales Customer_name
Office Supplies Central Chicago United States 15.696 Suzanne McNair
Technology East New York City United States 539.97 Amy Hunt
Office Supplies East New York City United States 22.58 Amy Hunt
Office Supplies East Dover United States 16.52 Emily Phan
Office Supplies East Dover United States 671.94 Emily Phan
Technology West Aurora United States 169.064 Tracy Poddar
Office Supplies West Aurora United States 168.624 Tracy Poddar
Office Supplies East Long Beach United States 19.44 Anthony Rawles
Office Supplies West Pasadena United States 31.92 Helen Andreada
Furniture West Pasadena United States 433.568 Helen Andreada

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 –

Category Region City Country Customer_ID Customer_name Total_sales
Office Supplies Central Chicago United States SM-20950 Suzanne McNair 946.72
Office Supplies East New York City United States AH-10465 Amy Hunt 946.72
Office Supplies East Dover United States EP-13915 Emily Phan 946.72
Office Supplies East Dover United States EP-13915 Emily Phan 946.72
Office Supplies West Aurora United States TP-21565 Tracy Poddar 946.72
Office Supplies East Long Beach United States AR-10825 Anthony Rawles 946.72
Office Supplies West Pasadena United States HA-14920 Helen Andreada 946.72
Technology East New York City United States AH-10465 Amy Hunt 709.034
Technology West Aurora United States TP-21565 Tracy Poddar 709.034
Furniture West Pasadena United States HA-14920 Helen Andreada 433.568

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

Automate data analysis pipeline and create report ready dashboards

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.